package com.hongdee.insurance.order.service.impl;

import com.hongdee.common.util.DateUtils;
import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.insurance.Constant;
import com.hongdee.insurance.company.service.ITInsuranceCompanyService;
import com.hongdee.insurance.order.dao.ITInsuranceOrderDao;
import com.hongdee.insurance.order.service.ITInsuranceOrderStatsticsService;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author 王帅
 * @className TInsuranceOrderStaticsServiceImpl
 * @type
 * @description 保单统计信息
 * @path com.hongdee.insurance.order.service.impl
 * @create 2017/7/3 10:15
 **/
@Service
public class TInsuranceOrderStaticsServiceImpl implements ITInsuranceOrderStatsticsService {

    @Autowired
    private ITInsuranceOrderDao orderDao;

    @Autowired
    private ITInsuranceCompanyService companyService;

    /**
     * 将保单新增入统计信息表
     * @param companyId
     * @throws Exception
     */
    @Override
    public void addOrderToStatistics(String companyId,String orderId) throws Exception {
        StringBuffer sql=new StringBuffer();
        sql.append("INSERT INTO T_INSURANCE_ORDER_STATISTICS (back_commission,id,create_time,create_user,update_time,update_user,company_id," +
                " back_date,back_type,base_type,car_id,discount_info,insurance_commissioner_name,insurance_company_id," +
                " insured,out_order_date,pay_num,pay_people,pay_type,quotation_id,remark,STATUS,return_date,return_type," +
                " contact_phone,back_Price,commission_date,commission_type,price_warning_type,insure_type,dept_id,commission_remark,plate_num,vin," +
                " insurance_company_name,u_name,sy_start_date,sy_stop_date,sy_commission,sy_commission_temp,sy_is_commission_temp," +
                " sy_commission_price,sy_contract_no,sy_price,jq_start_date,jq_stop_date,jq_commission,jq_commission_temp," +
                " jq_is_commission_temp,jq_commission_price,jq_contractNo,jq_price,car_brand,car_series,car_model,o_name," +
                " o_address,o_car_brand,t_disp,t_value,iscompulsoryinsurance,insurance_type,sy_add_commission,jq_add_commission,distcount," +
                " insurance_id,buy_date,car_label,continuous,sy_add_commission_price,jq_add_commission_price,insurance_commissioner_id," +
                " store_name,store_p_area,store_area,store_chanel,store_area_code,store_p_area_code,store_code," +
                " ccs_commission,ccs_commission_temp,ccs_is_commission_temp,ccs_commission_price,ccs_price)" +

                " SELECT tio.back_commission,tio.id,tio.create_time,tio.create_user,tio.update_time,tio.update_user,tio.company_id,tio.back_date,tio.back_type,tio.base_type," +
                " tio.car_id,tio.discount_info,tio.insurance_commissioner_name,tio.insurance_company_id," +
                " tio.insured,tio.out_order_date,tio.pay_num,tio.pay_people,tio.pay_type,tio.quotation_id,tio.remark,tio.status,tio.return_date," +
                " tio.return_type,tio.contact_phone,tio.back_Price,tio.commission_date,tio.commission_type,tio.price_warning_type,tio.insure_type," +
                " tio.dept_id,tio.commission_remark,tc.plate_num, tc.vin, tic.insurance_company_name, u.u_name, sy.start_date syStartDate," +
                " sy.stop_date syStopDate, sy.commission syCommission, sy.commission_temp syCommissionTemp, " +
                " sy.is_commission_temp syIsCommissionTemp, sy.commission_price syCommissionPrice, sy.contract_no syContractNo," +
                " case when sy.price is null then '0' else sy.price END syPrice, jq.start_date jqStartDate," +
                " jq.stop_date jqStopDate, jq.commission jqCommission," +
                " jq.commission_temp jqCommissionTemp, jq.is_commission_temp jqIsCommissionTemp, jq.commission_price jqCommissionPrice," +
                " jq.contract_no jqContractNo,case when jq.price is null then '0' else jq.price END jqPrice, tc.car_brand, tc.car_series, tc.car_model, company.o_name," +
                " company.o_address, company.o_car_brand, company.label, company.code,tio.iscompulsoryinsurance,tio.insurance_type," +
                " case when sy.add_commission is null then '0' else sy.add_commission END syAddCommission," +
                " case when jq.add_commission is null then '0' else jq.add_commission END jqAddCommission,tio.distcount,tio.insurance_id," +
                " tc.first_record_date,tc.label,case when tio.continuous='true' then tio.continuous else 'false' end AS continuous," +
                " case when sy.add_commission_price is null then '0' else sy.add_commission_price END syAddCommissionPrice," +
                " case when jq.add_commission_price is null then '0' else jq.add_commission_price END jqAddCommissionPrice," +
                " tio.insurance_commissioner_id,tio.store_name,tio.store_p_area,tio.store_area,tio.store_chanel," +
                " tio.store_area_code,tio.store_p_area_code,tio.store_code " +
                " ,ccs.commission ccsCommission,ccs.commission_temp ccsCommissionTemp,ccs.is_commission_temp ccsIsCommissionTemp,ccs.commission_price ccsCommissionPrice," +
                " CASE WHEN ccs.price IS NULL THEN '0' ELSE ccs.price END ccsPrice" +
                " FROM T_Insurance_Order tio " +
                " LEFT JOIN t_insurance_car tc ON tio.car_id = tc.id " +
                " LEFT JOIN(select o.id,o.o_car_brand,o.o_address,o.o_name,a.* from t_bc_organ o," +
                "   (select label,code from t_bc_newdict where p_code='area') a where o.o_area <>'' and o.o_area=a.code) company " +
                " ON tio.company_id=company.id " +
                " LEFT JOIN (SELECT * FROM t_insurance_order_item WHERE contract_type = 'jq' ) AS jq ON tio.id = jq.order_id " +
                " LEFT JOIN (SELECT * FROM t_insurance_order_item WHERE contract_type = 'sy' ) AS sy ON tio.id = sy.order_id " +
                " LEFT JOIN ( SELECT * FROM t_insurance_order_item WHERE contract_type = 'ccs' ) AS ccs ON tio.id = ccs.order_id " +
                " LEFT JOIN t_insurance_company tic ON tio.insurance_company_id = tic.id " +
                " LEFT JOIN t_bc_user u ON u.id = tio.create_user  " +
                " where not EXISTS(select * from T_INSURANCE_ORDER_STATISTICS where id=tio.id) and tio.company_id=? and tio.id=?");
        this.orderDao.executeSQL(sql.toString(),companyId,orderId);
        this.updCouponPrice(companyId);

        String brandSql="update t_insurance_order_statistics s,t_bc_brand b set s.car_brand_name=b.b_name where s.o_car_brand=b.b_code";
        this.orderDao.executeSQL(brandSql);
        this.orderDao.executeSQL("update t_insurance_order_statistics set sy_price='0' where sy_price is null");
        this.orderDao.executeSQL("update t_insurance_order_statistics set jq_price='0' where jq_price is null");
    }

    @Override
    public void updOrderToStatistics(String companyId) throws Exception{
        this.updOrderToStatistics("",companyId);
    }
    @Override
    public void updOrderToStatistics(String orderId, String companyId) throws Exception {
        StringBuffer sql=new StringBuffer();
        sql.append("update t_insurance_order_statistics st,(" +
                " SELECT tio.*,tic.insurance_company_name,u.u_name,sy.start_date syStartDate," +
                " sy.stop_date syStopDate,sy.commission syCommission,sy.commission_temp syCommissionTemp,sy.is_commission_temp syIsCommissionTemp," +
                " sy.commission_price syCommissionPrice,sy.contract_no syContractNo,sy.price syPrice,jq.start_date jqStartDate," +
                " jq.stop_date jqStopDate,jq.commission jqCommission,jq.commission_temp jqCommissionTemp,jq.is_commission_temp jqIsCommissionTemp," +
                " jq.commission_price jqCommissionPrice,jq.contract_no jqContractNo,jq.price jqPrice,tc.car_brand,tc.car_series,tc.car_model," +
                " company.o_name,company.o_address,company.o_car_brand,company.label,company.code," +
                " case when sy.add_commission is null then '0' else sy.add_commission END syAddCommission," +
                " case when jq.add_commission is null then '0' else jq.add_commission END jqAddCommission," +
                " case when sy.add_commission_price is null then '0' else sy.add_commission_price END syAddCommissionPrice," +
                " case when jq.add_commission_price is null then '0' else jq.add_commission_price END jqAddCommissionPrice, " +
                " tc.first_record_date,tc.label carLable " +
                " FROM" +
                " T_Insurance_Order tio" +
                " LEFT JOIN t_insurance_car tc ON tio.car_id = tc.id" +
                " LEFT JOIN (SELECT o.id,o.o_car_brand,o.o_address,o.o_name,a.* FROM t_bc_organ o, (" +
                " SELECT label,code FROM t_bc_newdict WHERE p_code = 'area' ) a WHERE o.o_area <> '' AND o.o_area = a.code ) company ON tio.company_id = company.id" +
                " LEFT JOIN (SELECT * FROM t_insurance_order_item WHERE contract_type = 'jq' ) AS jq ON tio.id = jq.order_id" +
                " LEFT JOIN (SELECT * FROM t_insurance_order_item WHERE contract_type = 'sy' ) AS sy ON tio.id = sy.order_id" +
                " LEFT JOIN t_insurance_company tic ON tio.insurance_company_id = tic.id" +
                " LEFT JOIN t_bc_user u ON u.id = tio.create_user" +
                " ) o" +
                " set st.sy_commission=o.syCommission,st.sy_commission_price=o.syCommissionPrice," +
                " st.sy_commission_temp=o.syCommissionTemp,st.sy_contract_no=o.syContractNo," +
                " st.sy_is_commission_temp=o.syIsCommissionTemp,st.sy_price=o.syPrice," +
                " st.sy_start_date=o.syStartDate,st.sy_stop_date=o.syStopDate," +
                " st.jq_commission=o.jqCommission,st.jq_commission_price=o.jqCommissionPrice," +
                " st.jq_commission_temp=o.jqCommissionTemp,st.jq_contractNo=o.jqContractNo," +
                " st.jq_is_commission_temp=o.jqIsCommissionTemp,st.jq_price=o.jqPrice," +
                " st.jq_start_date=o.jqStartDate,st.jq_stop_date=o.jqStopDate," +
                " st.back_Price=o.back_price,st.back_date=o.back_date," +
                " st.back_type=o.back_type," +
                " st.insure_type=o.insure_type,st.commission_type=o.commission_type," +
                " st.price_warning_type=o.price_warning_type," +
                " st.iscompulsoryinsurance=o.iscompulsoryinsurance, " +
                " st.sy_add_commission=o.syAddCommission," +
                " st.jq_add_commission=o.jqAddCommission, " +
                " st.distcount=o.distcount," +
                " st.insurance_type=o.insurance_type, "+
                " st.insurance_id=o.insurance_id," +
                " st.buy_date=o.first_record_date," +
                " st.car_label=o.carLable," +
                " st.continuous=if(o.continuous='true',o.continuous,'false')," +
                " st.sy_add_commission_price=o.syAddCommissionPrice, "+
                " st.jq_add_commission_price=o.jqAddCommissionPrice, "+
                " st.insurance_commissioner_id=o.insurance_commissioner_id," +
                " st.insurance_commissioner_name=o.insurance_commissioner_name," +
                " st.store_name=o.store_name," +
                " st.store_p_area=o.store_p_area," +
                " st.store_area=o.store_area," +
                " st.store_chanel=o.store_chanel," +
                " st.store_area_code=o.store_area_code," +
                " st.store_p_area_code=o.store_p_area," +
                " st.store_code=o.store_code "+
                " where st.id=o.id ");
        List<String> params=new ArrayList<>();
        if(!"".equals(orderId)){
            sql.append(" and o.id=?  ");
            params.add(orderId);
        }
        sql.append("and o.company_id=? ");
        params.add(companyId);
        this.orderDao.executeSQL(sql.toString(),params.toArray());
        this.updCouponPrice(companyId);
    }

    private void updCouponPrice(String companyId) throws Exception {
        StringBuffer sqlCou=new StringBuffer();
        sqlCou.append(" update  t_insurance_order_statistics s,( " +
                " select ROUND(SUM(a.cost),2) couponPrice,cou.quotation_id,cou.company_id," +
                " GROUP_CONCAT(CONCAT(a.`name`,'[',a.cost,'￥]') separator ';' ) couponItems  " +
                " from t_insurance_order_coupon cou left JOIN t_crm_car_activity a " +
                " ON cou.activity_id=a.id group by quotation_id )coupon set s.coupon_price=coupon.couponPrice," +
                " s.coupon_itmes=coupon.couponItems " +
                " where s.quotation_id =coupon.quotation_id and coupon.company_id=? ");
        this.orderDao.executeSQL(sqlCou.toString(),companyId);
    }

    /**
     * 根据条件查询日周报表
     * @param uid
     * @param type
     * @param time
     * @param brand 品牌
     * @param companyId 4s店
     * @param dict 区域
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map<String,Object> dayAndWeek(String uid,String type,String time,String brand,String companyId,String dict,String start,String limit) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params= new ArrayList<>();
        this.dayAndWeekQueryToSql(sql,uid,params,companyId,time,brand,dict,type);
        return this.orderDao.page(sql.toString(),params.toArray(),start,limit);
    }

    /**
     * 日 周报表导出excel
     * @param uid
     * @param type
     * @param time
     * @param brand
     * @param companyId
     * @param dict
     * @return
     * @throws Exception
     */
    @Override
    public XSSFWorkbook dayAndWeekExpExcel(String uid,String type,String time,String brand,String companyId,String dict) throws Exception {
        String typeName="当天";
        if(type.equals(Constant.STATSTICS_TYPE.week.name())){
            typeName="当周";
        }
        StringBuffer sql=new StringBuffer();
        List<String> params= new ArrayList<>();
        this.dayAndWeekQueryToSql(sql,uid,params,companyId,time,brand,dict,type);
        List<Map<String,Object>> mapList=this.orderDao.list(sql.toString(),params.toArray());
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("报表统计--"+typeName+"报表统计");
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中 
        //合并单元格
        CellRangeAddress cell1=new CellRangeAddress(0,1,0,0);
        CellRangeAddress cell2=new CellRangeAddress(0,1,1,1);
        CellRangeAddress cell3=new CellRangeAddress(0,1,2,2);
        CellRangeAddress cell4=new CellRangeAddress(0,0,3,9);
        CellRangeAddress cell5=new CellRangeAddress(0,0,10,17);
        sheet.addMergedRegion(cell1);
        sheet.addMergedRegion(cell2);
        sheet.addMergedRegion(cell3);
        sheet.addMergedRegion(cell4);
        sheet.addMergedRegion(cell5);

        Row row1 = sheet.createRow(0);
        Cell headCell=row1.createCell(0);
        headCell.setCellValue("品牌");
        headCell.setCellStyle(cellStyle);

        headCell=row1.createCell(1);
        headCell.setCellValue("区域");
        headCell.setCellStyle(cellStyle);

        headCell=row1.createCell(2);
        headCell.setCellValue("4S店");
        headCell.setCellStyle(cellStyle);

        headCell=row1.createCell(3);
        headCell.setCellValue(typeName);
        headCell.setCellStyle(cellStyle);

        headCell=row1.createCell(10);
        headCell.setCellValue("当月累计个数");
        headCell.setCellStyle(cellStyle);
        XSSFRow row = sheet.createRow((int) 1);
        String[] excelHeaderRecordout = {"新保个数", "新保保费","新保代理费", "续保保费", "续保个数","续保代理费", typeName+"总保单数",
                "新保个数", "新保保费","新保代理费", "续保保费", "续保个数","续保代理费", "当月总保单数"};
        for (int i = 0; i < excelHeaderRecordout.length; i++) {
            XSSFCell cell = row.createCell(i+3);
            cell.setCellValue(excelHeaderRecordout[i]);
            cell.setCellStyle(cellStyle);
            sheet.autoSizeColumn(i+3);
            sheet.setDefaultRowHeightInPoints(24);
            sheet.setColumnWidth(cell.getColumnIndex(), 100 * 30);
        }
        for (int i = 0; i < mapList.size(); i++) {
            Map<String,Object> map=mapList.get(i);
            row = sheet.createRow(i + 2);
            row.createCell(0).setCellValue(StringUtil.formatEmpty(map.get("carBrand")));
            row.createCell(1).setCellValue(StringUtil.formatEmpty(map.get("disp")));
            row.createCell(2).setCellValue(StringUtil.formatEmpty(map.get("oName")));
            row.createCell(3).setCellValue(StringUtil.formatEmpty(map.get("sbCount")));
            row.createCell(4).setCellValue(StringUtil.formatEmpty(map.get("sbPrice")));
            row.createCell(5).setCellValue(StringUtil.formatEmpty(map.get("sbCommisssionPrice")));
            row.createCell(6).setCellValue(StringUtil.formatEmpty(map.get("xbPrice")));
            row.createCell(7).setCellValue(StringUtil.formatEmpty(map.get("xbCount")));
            row.createCell(8).setCellValue(StringUtil.formatEmpty(map.get("xbCommisssionPrice")));
            row.createCell(9).setCellValue(StringUtil.formatEmpty(map.get("count")));
            row.createCell(10).setCellValue(StringUtil.formatEmpty(map.get("mSbCount")));
            row.createCell(11).setCellValue(StringUtil.formatEmpty(map.get("mSbPrice")));
            row.createCell(12).setCellValue(StringUtil.formatEmpty(map.get("mSbCommissionPrice")));
            row.createCell(13).setCellValue(StringUtil.formatEmpty(map.get("mXbprice")));
            row.createCell(14).setCellValue(StringUtil.formatEmpty(map.get("xbCount")));
            row.createCell(15).setCellValue(StringUtil.formatEmpty(map.get("mxbCommissionPrice")));
            row.createCell(16).setCellValue(StringUtil.formatEmpty(map.get("mTotalCount")));

        }
        return wb;
    }

    /**
     * 日 周报表查询sql
     * 增加追单统计（追单个数、追单金额）、退单统计（退单个数、退单金额）by wangshuai date 2017-10-10
     * @param sql
     * @param uid
     * @param params
     * @param companyId
     * @param time
     * @param brand
     * @param dict
     * @param type
     * @throws Exception
     */
    private void dayAndWeekQueryToSql(StringBuffer sql,String uid,List<String> params,String companyId,String time,String brand,String dict,String type) throws Exception {
        String sb="insurance_type='0' and insure_type='sb'";
        String xb="insurance_type='0' and insure_type='xb'";
        sql.append("select g.company_id,g.o_name,g.car_brand,g.t_disp disp,g.car_brand_name,d.totalCount,d.jqTotalCount," +
                " d.dJqPrices,d.allPrices,d.syPrices,d.jqPrices,d.xbCount,d.xbJqTotalCount,d.xbAllPrices," +
                " ROUND(d.xbDJqPrices-d.xbDJqVVUPrices,2) xbDJqPrices,d.xbDJqVVUPrices," +
                " d.xbSyPrices,d.xbJqPrices,d.sbCount,d.sbJqTotalCount,d.sbAllPrices," +
                " ROUND(d.sbDJqPrices-d.sbDJqVVUPrices,2) sbDJqPrices,d.sbDJqVVUPrices,d.sbSyPrices,d.sbJqPrices, " +
                " d.sbAddCount,d.sbAddPrice,d.xbAddCount,d.xbAddPrice,d.sbBackCount,d.sbBackPrice,d.xbBackCount,d.xbBackPrice,"+
                " d.sbVVULPT,d.xbVVULPT,ROUND(d.xbAllPrices-d.xbVVULPT,2) xbAllNVVPrice,ROUND(d.sbAllPrices-d.sbVVULPT,2) sbAllNVVPrice,"+
                " m.totalCount mtotalCount,m.jqTotalCount mjqTotalCount,m.dJqPrices mdJqPrices,m.allPrices mallPrices," +
                " m.syPrices msyPrices,m.jqPrices mjqPrices,m.xbCount mxbCount,m.xbJqTotalCount mxbJqTotalCount," +
                " m.xbAllPrices mxbAllPrices,ROUND(m.xbDJqPrices-m.xbDJqVVUPrices,2) mxbDJqPrices,m.xbDJqVVUPrices mxbDJqVVUPrices," +
                " m.xbSyPrices mxbSyPrices,m.xbJqPrices mxbJqPrices," +
                " m.sbCount msbCount,m.sbJqTotalCount msbJqTotalCount,m.sbAllPrices msbAllPrices," +
                " ROUND(m.sbDJqPrices-m.sbDJqVVUPrices,2) msbDJqPrice,m.sbDJqVVUPrices msbDJqVVUPrices," +
                " m.sbSyPrices msbSyPrices,m.sbJqPrices msbJqPrices, " +
                " m.sbAddCount msbAddCount,m.sbAddPrice msbAddPrice,m.xbAddCount mxbAddCount,m.xbAddPrice mxbAddPrice," +
                " m.sbBackCount msbBackCount,m.sbBackPrice msbBackPrice,m.xbBackCount mxbBackCount,m.xbBackPrice mxbBackPrice,"+
                " m.sbVVULPT msbVVULPT,m.xbVVULPT mxbVVULPT," +
                " ROUND(m.xbAllPrices-m.xbVVULPT,2) mxbAllNVVPrice,ROUND(m.sbAllPrices-m.sbVVULPT,2) msbAllNVVPrice "+
                " from (" +
                " select company_id,o_name,car_brand,t_value,t_disp,car_brand_name from t_insurance_order_statistics " +
                " where 1=1  " );
        if("".equals(companyId)){
            Constant.setQueryByRoleDate(uid,sql,params,"");
        }else{
            sql.append("and company_id=? ");
            params.add(companyId);
        }
        //当天统计报表
        sql.append(" group by company_id,o_car_brand,t_value) g" +
                " left join (" +
                " SELECT COUNT(if(insurance_type='0',true,null)) totalCount," +
                " o_name,o_car_brand car_brand,t_disp disp," +
                " COUNT(if(insurance_type='0' and iscompulsoryinsurance='0' and continuous='false',true,NULL)) jqTotalCount," +
                " ROUND(SUM(if(insurance_type='0' and iscompulsoryinsurance='0',jq_price * 1,0)), 2) dJqPrices," +
                " ROUND(SUM(if(insurance_type='0',sy_price * 1+jq_price*1,0)), 2) allPrices," +
                " ROUND(SUM(sy_price * 1), 2) syPrices," +
                " ROUND(SUM(jq_price * 1), 2) jqPrices," +
                " COUNT(if("+xb+",true,null)) xbCount," +
                " COUNT(if("+xb+" and iscompulsoryinsurance='0',true,NULL)) xbJqTotalCount," +
                " ROUND(SUM(if("+xb+",sy_price * 1+jq_price * 1,0)), 2) xbAllPrices," +
                " ROUND(SUM(IF("+xb+" and iscompulsoryinsurance = '0',jq_price * 1,0)),2) xbDJqPrices, " +
                " ROUND(SUM(if("+xb+",sy_price * 1,0)), 2) xbSyPrices," +
                " ROUND(SUM(if("+xb+",jq_price * 1,0)), 2) xbJqPrices," +
                " COUNT(if("+sb+",true,null)) sbCount," +
                " COUNT(if("+sb+" and iscompulsoryinsurance='0' and continuous='false',true,NULL)) sbJqTotalCount," +
                " ROUND(SUM(if("+sb+",sy_price * 1+jq_price * 1,0)), 2) sbAllPrices," +
                " ROUND(SUM(IF("+sb+" and iscompulsoryinsurance = '0',jq_price * 1,0)),2) sbDJqPrices, " +
                " ROUND(SUM(if("+sb+",sy_price * 1,0)), 2) sbSyPrices," +
                " ROUND(SUM(if("+sb+",jq_price * 1,0)), 2) sbJqPrices," +
                " COUNT(if(insurance_type='1' and insure_type='sb',true,null)) sbAddCount, "+
                " ROUND(sum(if(insurance_type='1' and insure_type='sb',sy_price,0)),2) sbAddPrice, "+
                " COUNT(if(insurance_type='1' and insure_type='xb',true,null)) xbAddCount, "+
                " ROUND(sum(if(insurance_type='1' and insure_type='xb',sy_price,0)),2) xbAddPrice, "+

                " COUNT(if(insurance_type='2' and insure_type='sb',true,null)) sbBackCount, "+
                " ROUND(sum(if(insurance_type='2' and insure_type='sb',sy_price+jq_price,0)),2) sbBackPrice, "+
                " COUNT(if(insurance_type='2' and insure_type='xb',true,null)) xbBackCount, "+
                " ROUND(sum(if(insurance_type='2' and insure_type='xb',sy_price+jq_price,0)),2) xbBackPrice, "+
                " company_id,t_value," +
                " ROUND(sum(if(insurance_type='0' and insure_type='sb',items.item_price,0)),2) sbVVULPT," +
                " ROUND(sum(if(insurance_type='0' and insure_type='xb',items.item_price,0)),2) xbVVULPT," +
                " ROUND(SUM(IF("+xb+" and iscompulsoryinsurance = '0',items.item_price * 1,0)),2) xbDJqVVUPrices, " +
                " ROUND(SUM(IF("+sb+" and iscompulsoryinsurance = '0',items.item_price * 1,0)),2) sbDJqVVUPrices " +
                " FROM" +
                " t_insurance_order_statistics s,(SELECT if(item_id='0',item_price,0) item_price,quotation_id  " +
                " FROM t_insurance_quotation_item WHERE company_id=? group by quotation_id) items " +
                " WHERE s.quotation_id=items.quotation_id ");
        params.add(companyId);
        this.dayAndWeekQuaryParams(uid,sql,params,companyId,time,brand,dict,type,"");
        //当月统计报表
        sql.append(" GROUP BY company_id,o_car_brand,t_value) d" +
                " ON g.company_id=d.company_id " +
//                " and g.car_brand=d.car_brand and g.t_value=d.t_value" +
                " left join (" +
                " SELECT COUNT(if(insurance_type='0',true,null)) totalCount," +
                " o_name,o_car_brand car_brand,t_disp disp," +
                " COUNT(if(insurance_type='0' and iscompulsoryinsurance='0' and continuous='false',true,NULL)) jqTotalCount," +
                " ROUND(SUM(if(insurance_type='0' and iscompulsoryinsurance='0',jq_price * 1,0)), 2) dJqPrices," +
                " ROUND(SUM(if(insurance_type='0',sy_price * 1+jq_price*1,0)), 2) allPrices," +
                " ROUND(SUM(if(insurance_type='0',sy_price * 1,0)), 2) syPrices," +
                " ROUND(SUM(if(insurance_type='0',jq_price * 1,0)), 2) jqPrices," +
                " COUNT(if("+xb+",true,null)) xbCount," +
                " COUNT(if("+xb+" and iscompulsoryinsurance='0' and continuous='false' ,true,NULL)) xbJqTotalCount," +
                " ROUND(SUM(if("+xb+",sy_price * 1+jq_price * 1,0)), 2) xbAllPrices," +
                " ROUND(SUM(IF("+xb+" and iscompulsoryinsurance = '0',jq_price * 1,0)),2) xbDJqPrices," +
                " ROUND(SUM(if("+xb+",sy_price * 1,0)), 2) xbSyPrices," +
                " ROUND(SUM(if("+xb+",jq_price * 1,0)), 2) xbJqPrices," +
                " COUNT(if("+sb+",true,null)) sbCount," +
                " COUNT(if("+sb+" and iscompulsoryinsurance='0' and continuous='false',true,NULL)) sbJqTotalCount," +
                " ROUND(SUM(if("+sb+",sy_price * 1+jq_price * 1,0)), 2) sbAllPrices," +
                " ROUND(SUM(IF("+sb+" and iscompulsoryinsurance = '0',jq_price * 1,0)),2) sbDJqPrices," +
                " ROUND(SUM(if("+sb+",sy_price * 1,0)), 2) sbSyPrices," +
                " ROUND(SUM(if("+sb+",jq_price * 1,0)), 2) sbJqPrices," +
                " COUNT(if(insurance_type='1' and insure_type='sb',true,null)) sbAddCount, "+
                " ROUND(sum(if(insurance_type='1' and insure_type='sb',sy_price,0)),2) sbAddPrice, "+
                " COUNT(if(insurance_type='1' and insure_type='xb',true,null)) xbAddCount, "+
                " ROUND(sum(if(insurance_type='1' and insure_type='xb',sy_price,0)),2) xbAddPrice, "+

                " COUNT(if(insurance_type='2' and insure_type='sb',true,null)) sbBackCount, "+
                " ROUND(sum(if(insurance_type='2' and insure_type='sb',sy_price+jq_price,0)),2) sbBackPrice, "+
                " COUNT(if(insurance_type='2' and insure_type='xb',true,null)) xbBackCount, "+
                " ROUND(sum(if(insurance_type='2' and insure_type='xb',sy_price+jq_price,0)),2) xbBackPrice, "+
                " company_id, t_value," +
                " ROUND(sum(if("+sb+",items.item_price,0)),2) sbVVULPT," +
                " ROUND(sum(if("+xb+",items.item_price,0)),2) xbVVULPT," +
                " ROUND(SUM(IF("+xb+" and iscompulsoryinsurance = '0',items.item_price * 1,0)),2) xbDJqVVUPrices, " +
                " ROUND(SUM(IF("+sb+" and iscompulsoryinsurance = '0',items.item_price * 1,0)),2) sbDJqVVUPrices " +
                " FROM" +
                " t_insurance_order_statistics s,(SELECT if(item_id='0',item_price,0) item_price,quotation_id  " +
                " FROM t_insurance_quotation_item WHERE company_id=? group by quotation_id ) items " +
                " WHERE s.quotation_id=items.quotation_id " );
        params.add(companyId);
        //当月----------------------------------------------
        if("".equals(time)){
            time=DateUtils.getTimeStr();
        }
        Long t1=DateUtils.monthSToLongTime(time);
        String time1=DateUtils.formatLongTime(t1,"yyyy-MM-dd");
        Long t2=DateUtils.addMonthLongTime(t1,+1);
        String time2=DateUtils.formatLongTime(t2,"yyyy-MM-dd");
        this.monthQuaryParams(uid,sql,params,companyId,time1,time2,brand,dict);
        sql.append(" GROUP BY company_id,o_car_brand,t_value) m" +
                " ON g.company_id=m.company_id " +
//                " and g.car_brand=m.car_brand and g.t_value=m.t_value" +
                "");
        
        
        
        
//        //当天或当周                新保个数           当月交强险总保费         当月商业险总保费        续保个数            续保交强险总保费           续保商业险总保费
//        sql.append(" select t.*,t2.sbCount mSbCount,t2.jqPrices mSbJqPrice,t2.syPrices mSbSyprice,t2.sbPrice mSbPrice," +
//                " t2.sbCommisssionPrice mSbCommissionPrice,t2.xbCount mXbCount,t2.xbJqPrice mXbJpPrice,t2.xbSyPrice mXbSyPrice," +
//                " t2.xbPrice mXbprice,t2.xbCommisssionPrice mxbCommissionPrice,t2.totalCount mTotalCount from ( ");
//        sql.append(" select z.*," +
//                " sb.sbCount,sb.sbJqPrice,sb.sbSyPrice,ROUND(sb.sbJqPrice+sb.sbSyPrice,2) sbPrice,sb.sbJqCommissionPrice,sb.sbSyCommissionPrice," +
//                " ROUND(sb.sbJqCommissionPrice+sb.sbSyCommissionPrice,2) sbCommisssionPrice," +
//                " xb.xbCount,xb.xbJqPrice,xb.xbSyPrice,ROUND(xb.xbJqPrice+xb.xbSyPrice,2) xbPrice,xb.xbJqCommissionPrice,xb.xbSyCommissionPrice," +
//                " ROUND(xb.xbJqCommissionPrice+xb.xbSyCommissionPrice,2) xbCommisssionPrice,(sb.sbCount+xb.xbCount) count " +
//                " from (" +
//                " select COUNT(id) totalCount,o_name,o_car_brand car_brand,t_disp disp,ROUND(SUM(sy_price*1),2) syPrices,ROUND(SUM(jq_price*1),2) jqPrices,company_id,t_value" +
//                " from t_insurance_order_statistics ");
//        sql.append(" where 1=1 ");
//
//        this.dayAndWeekQuaryParams(uid,sql,params,companyId,time,brand,dict,type,"");
//
//        sql.append(
//                " group by company_id,o_car_brand,t_value" +
//                        " ) z" +
//                        " left join (" +
//                        " select id,COUNT(id) sbCount,ROUND(SUM(jq_price*1),2) sbJqPrice,ROUND(SUM(sy_price*1),2) sbSyPrice,ROUND(SUM(jq_commission_price*1),2) sbJqCommissionPrice," +
//                        " ROUND(SUM(sy_commission_price*1),2) sbSyCommissionPrice,company_id,o_car_brand car_brand,t_value from t_insurance_order_statistics where insure_type='sb' ");
//
//        this.dayAndWeekQuaryParams(uid,sql,params,companyId,time,brand,dict,type,"");
//
//        sql.append(" group by company_id,o_car_brand,t_value ) sb" +
//                " ON" +
//                " z.company_id=sb.company_id and z.car_brand=sb.car_brand and z.t_value=sb.t_value" +
//                " left join (" +
//                " select id,COUNT(id) xbCount,ROUND(SUM(jq_price*1),2) xbJqPrice,ROUND(SUM(sy_price*1),2) xbSyPrice,ROUND(SUM(jq_commission_price*1),2) xbJqCommissionPrice," +
//                " ROUND(SUM(sy_commission_price*1),2) xbSyCommissionPrice,company_id,o_car_brand car_brand,t_value from t_insurance_order_statistics where insure_type='xb' ");
//
//        this.dayAndWeekQuaryParams(uid,sql,params,companyId,time,brand,dict,type,"");
//
//        sql.append(" group by company_id,o_car_brand,t_value ) xb" +
//                " ON" +
//                " z.company_id=xb.company_id and z.car_brand=xb.car_brand and z.t_value=xb.t_value");
//        sql.append(" )t ");
//
//        //当月----------------------------------------------
//        if("".equals(time)){
//            time=DateUtils.getTimeStr();
//        }
//        Long t1=DateUtils.monthSToLongTime(time);
//        String time1=DateUtils.formatLongTime(t1,"yyyy-MM-dd");
//        Long t2=DateUtils.addMonthLongTime(t1,+1);
//        String time2=DateUtils.formatLongTime(t2,"yyyy-MM-dd");
//        sql.append(" left join ( ");
//        sql.append(" SELECT z.*, " +
//                " sb.sbCount,sb.sbJqPrice,sb.sbSyPrice,ROUND(sb.sbJqPrice+sb.sbSyPrice,2) sbPrice,sb.sbJqCommissionPrice,sb.sbSyCommissionPrice," +
//                " ROUND(sb.sbJqCommissionPrice+sb.sbSyCommissionPrice,2) sbCommisssionPrice," +
//                " xb.xbCount,xb.xbJqPrice,xb.xbSyPrice,ROUND(xb.xbJqPrice+xb.xbSyPrice,2) xbPrice,xb.xbJqCommissionPrice,xb.xbSyCommissionPrice," +
//                " ROUND(xb.xbJqCommissionPrice+xb.xbSyCommissionPrice,2) xbCommisssionPrice,(sb.sbCount+xb.xbCount) count " +
//                " from (" +
//                " select COUNT(id) totalCount,o_name,o_car_brand car_brand,t_disp disp,ROUND(SUM(sy_price*1),2) syPrices,ROUND(SUM(jq_price*1),2) jqPrices,company_id,t_value" +
//                " from t_insurance_order_statistics" +
//                " WHERE 1=1 ");
//
//        this.monthQuaryParams(uid,sql,params,companyId,time1,time2,brand,dict);
//
//        sql.append(
//                " GROUP BY company_id,o_car_brand,t_value" +
//                        " ) z" +
//                        " LEFT JOIN (" +
//                        " SELECT id,COUNT(id) sbCount,ROUND(SUM(jq_price * 1), 2) sbJqPrice," +
//                        " ROUND(SUM(sy_price * 1), 2) sbSyPrice,ROUND(SUM(jq_commission_price * 1),2) sbJqCommissionPrice," +
//                        " ROUND(SUM(sy_commission_price * 1),2) sbSyCommissionPrice,company_id,o_car_brand car_brand,t_value" +
//                        " FROM" +
//                        " t_insurance_order_statistics" +
//                        " WHERE insure_type = 'sb'");
//
//        this.monthQuaryParams(uid,sql,params,companyId,time1,time2,brand,dict);
//
//        sql.append( "GROUP BY company_id, o_car_brand, t_value" +
//                " ) sb ON z.company_id = sb.company_id AND z.car_brand = sb.car_brand AND z.t_value = sb.t_value" +
//                " LEFT JOIN (" +
//                " SELECT id, COUNT(id) xbCount," +
//                " ROUND(SUM(jq_price * 1), 2) xbJqPrice,ROUND(SUM(sy_price * 1), 2) xbSyPrice," +
//                " ROUND(SUM(jq_commission_price * 1),2) xbJqCommissionPrice," +
//                " ROUND(SUM(sy_commission_price * 1),2) xbSyCommissionPrice," +
//                " company_id,o_car_brand car_brand,t_value" +
//                " FROM" +
//                " t_insurance_order_statistics" +
//                " WHERE insure_type = 'xb'");
//
//        this.monthQuaryParams(uid,sql,params,companyId,time1,time2,brand,dict);
//
//        sql.append( "GROUP BY company_id, o_car_brand, t_value" +
//                " ) xb ON z.company_id = xb.company_id" +
//                " AND z.car_brand = xb.car_brand" +
//                " AND z.t_value = xb.t_value");
//        sql.append(" ) t2 ");
//        sql.append(" on t.car_brand=t2.car_brand and t.company_id=t2.company_id and t.t_value=t2.t_value");
    }

    /**
     * 参数生查询条件生成查询sql
     * @param uid
     * @param sql
     * @param paramList
     * @param companyId
     * @param time
     * @param brand
     * @param dict
     * @param type
     * @param insuranceType
     * @throws Exception
     */
    private void dayAndWeekQuaryParams(String uid,StringBuffer sql,List<String> paramList,String companyId,String time,
                                       String brand,String dict,String type,String insuranceType) throws Exception {
        //增加默认查询正常的保单
        if(!"".equals(companyId)){
            sql.append(" and company_id=? ");
            paramList.add(companyId);
        }else{
            Constant.setQueryByRoleDate(uid,sql,paramList,"");
        }
        if(Constant.STATSTICS_TYPE.day.name().equals(type)) {//当天
            if (!"".equals(time)) {
                sql.append(" and out_order_date=? ");
                paramList.add(time);
            } else {
                sql.append(" and out_order_date=DATE_FORMAT(NOW(),'%Y-%m-%d') ");
            }
        }else if(Constant.STATSTICS_TYPE.week.name().equals(type)){
            //当周
            String monday=DateUtils.getMonday(time);
            String weekDay=DateUtils.getWeekday(time);
            sql.append(" and out_order_date>=DATE_FORMAT(?,'%Y-%m-%d') and out_order_date<=DATE_FORMAT(?,'%Y-%m-%d') ");
            paramList.add(monday);
            paramList.add(weekDay);
        }else if(Constant.STATSTICS_TYPE.month.name().equals(type)){
            Long t1=DateUtils.monthSToLongTime(time);
            String time1=DateUtils.formatLongTime(t1,"yyyy-MM-dd");
            Long t2=DateUtils.addMonthLongTime(t1,+1);
            String time2=DateUtils.formatLongTime(t2,"yyyy-MM-dd");
            sql.append(" and out_order_date>=? and out_order_date<? ");
            paramList.add(time1);
            paramList.add(time2);
        }else if(Constant.STATSTICS_TYPE.beforeyear.name().equals(type)){
            Long t1=DateUtils.monthSToLongTime(time);
            String time1=DateUtils.formatLongTime(t1,"yyyy-MM-dd");
            Long t2=DateUtils.addMonthLongTime(t1,+1);
            String time2=DateUtils.formatLongTime(t2,"yyyy-MM-dd");
            sql.append(" and out_order_date>=DATE_SUB(?,INTERVAL 1 YEAR) and out_order_date<DATE_SUB(?,INTERVAL 1 YEAR) ");
            paramList.add(time1);
            paramList.add(time2);
        }
        if(!"".equals(brand)){
            sql.append(" and car_brand=? ");
            paramList.add(brand);
        }
        if(!"".equals(dict)){
            sql.append(" and t_value=? ");
            paramList.add(dict);
        }
        if(!"".equals(insuranceType)){
            sql.append(" and insure_type=? ");
            paramList.add(insuranceType);
        }
    }

    private void monthQuaryParams(String uid,StringBuffer sql,List<String> paramList,String companyId,String time1,String time2,String brand,String dict){
        sql.append(" and out_order_date>=? and out_order_date<? ");
        paramList.add(time1);
        paramList.add(time2);
        if(!"".equals(companyId)){
            sql.append(" and company_id=? ");
            paramList.add(companyId);
        }else{
            Constant.setQueryByRoleDate(uid,sql,paramList,"");
        }
        if(!"".equals(brand)){
            sql.append(" and car_brand=? ");
            paramList.add(brand);
        }
        if(!"".equals(dict)){
            sql.append(" and t_value=? ");
            paramList.add(dict);
        }
    }


    /**
     * 周保费分配查询
     * @param uid
     * @param time
     * @param companyId
     * @param insuranceType
     * @param dict
     * @param brand
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map<String,Object> weekInsurancePage(String uid,String time,String companyId,String insuranceType,String dict,String brand,
                                  String start,String limit) throws Exception {
        List insuranceCompanys=companyService.getCompanys();
        //1、根据统计信息表 根据条件查询出品牌、区域、4s店信息
        Map map=this.getBrandAndCompanyAndDisp(uid,time,companyId,insuranceType,dict,brand,start,limit,Constant.STATSTICS_TYPE.week.name());
        List<Map<String,Object>> bcdList= (List<Map<String, Object>>) map.get("dataList");
        //2、计算指定日期内的每个保险公司的保险个数及保费信息
        List<Map<String,Object>> insuranceCompanyWeekList=this.getInsuranceCompanyNumsByWeek(uid,time,companyId,insuranceType,dict,brand,Constant.STATSTICS_TYPE.week.name());
        List<Map<String,Object>> insuranceCompanyMonthList=this.getInsuranceCompanyNumsByWeek(uid,time,companyId,insuranceType,dict,brand,Constant.STATSTICS_TYPE.month.name());
        //3、遍历统计品牌、区域、4s店信息
        for(int i=0;i<bcdList.size();i++){
            //品牌 区域 4s店
            Map bcdMap=bcdList.get(i);
            //取出统计的品牌、区域、4s店信息
            String brandBcd= StringUtil.formatEmpty(bcdMap.get("brand"));
            String oNameBcd= StringUtil.formatEmpty(bcdMap.get("oName"));
            String dispBcd= StringUtil.formatEmpty(bcdMap.get("disp"));
            List<Map<String,Object>> weekComList=new ArrayList<>();
            List<Map<String,Object>> monthComList=new ArrayList<>();
            //4、遍历查询出来的保险公司信息
            Float weekPrice=0f;
            Float weekJqPrice=0f;
            int weekAmount=0;
            int weekJqAmount=0;
            int addAmount=0;
            Float addPrice=0f;
            int backAmount=0;
            float backPrice=0f;
            Float weekVvulpt=0f;
            for(Map incMap:insuranceCompanyWeekList){
                String brandInc= StringUtil.formatEmpty(incMap.get("brand"));
                String oNameInc= StringUtil.formatEmpty(incMap.get("oName"));
                String dispInc= StringUtil.formatEmpty(incMap.get("disp"));
                if(brandBcd.equals(brandInc) && oNameBcd.equals(oNameInc) && dispBcd.equals(dispInc)){
                    String id=StringUtil.formatEmpty(incMap.get("insuranceCompanyId"));
                    String allPrice=StringUtil.formatEmpty(incMap.get("allPrice"));
                    Float allPriceNVVPrice=0f;
                    if(!"".equals(allPrice)){
                        weekPrice+=Float.valueOf(allPrice);
                        allPriceNVVPrice=Float.valueOf(allPrice);

                    }
                    String amount=StringUtil.formatEmpty(incMap.get("amount"));
                    if(!"".equals(amount)){
                        weekAmount+=Integer.valueOf(amount);
                    }
                    String dJqPrice=StringUtil.formatEmpty(incMap.get("dJqPrice"));
                    if(!"".equals(dJqPrice)){
                        weekJqPrice+=Float.valueOf(dJqPrice);
                    }
                    String dJqAmount=StringUtil.formatEmpty(incMap.get("dJqAmount"));
                    if(!"".equals(dJqAmount)){
                        weekJqAmount+=Integer.valueOf(dJqAmount);
                    }
                    String addOrderAmount=StringUtil.formatEmpty(incMap.get("addOrderCount"));
                    if(!"".endsWith(addOrderAmount)){
                        addAmount+=Integer.valueOf(addOrderAmount);
                    }
                    String addOrderPrice=StringUtil.formatEmpty(incMap.get("addOrderPrice"));
                    if(!"".endsWith(addOrderPrice)){
                        addPrice+=Float.valueOf(addOrderPrice);
                    }
                    String backOrderAmount=StringUtil.formatEmpty(incMap.get("backOrderCount"));
                    if(!"".endsWith(backOrderAmount)){
                        backAmount+=Integer.valueOf(backOrderAmount);
                    }
                    String backOrderPrice=StringUtil.formatEmpty(incMap.get("backOrderPrice"));
                    if(!"".endsWith(backOrderPrice)){
                        backPrice+=Float.valueOf(backOrderPrice);
                    }
                    String VVULPT=StringUtil.formatEmpty(incMap.get("VVULPT"));
                    if(!"".equals(VVULPT)){
                        weekVvulpt+=Float.valueOf(VVULPT);
                        allPriceNVVPrice=Constant.formatFloat(allPriceNVVPrice-Float.valueOf(VVULPT));
                    }
                    String dJVVUqPrice=StringUtil.formatEmpty(incMap.get("dJVVUqPrice"));
                    bcdMap.put("week_"+id+"_amount",amount);
                    bcdMap.put("week_"+id+"_price",allPrice);
                    bcdMap.put("week_"+id+"_jqPrice",dJqPrice);
                    bcdMap.put("week_"+id+"_jqAmount",dJqAmount);
                    bcdMap.put("week_"+id+"_addAmount",addOrderAmount);
                    bcdMap.put("week_"+id+"_addPrice",addOrderPrice);
                    bcdMap.put("week_"+id+"_backAmount",backOrderAmount);
                    bcdMap.put("week_"+id+"_backPrice",backOrderPrice);
                    bcdMap.put("week_"+id+"_vvulptPrice",VVULPT);
                    bcdMap.put("week_"+id+"_priceNVVPrice",allPriceNVVPrice);
                    bcdMap.put("week_"+id+"_dJVVUqPrice",dJVVUqPrice);
                }
            }
            bcdMap.put("week_amount",weekAmount);
            bcdMap.put("week_price",weekPrice);
            bcdMap.put("week_jqAmount",weekJqAmount);
            bcdMap.put("week_jqPrice",weekJqPrice);
            bcdMap.put("week_addAmount",addAmount);
            bcdMap.put("week_addPrice",addPrice);
            bcdMap.put("week_backAmount",backAmount);
            bcdMap.put("week_backPrice",backPrice);
            bcdMap.put("week_vvulptPrice",weekVvulpt);
            bcdMap.put("week_nVVupltprice",Constant.formatFloat(weekPrice-weekVvulpt));
            monthInsuranceList(bcdMap,insuranceCompanyMonthList);
        }
        return map;
    }

    @Override
    public XSSFWorkbook weekAndMonthInsuranceExpExcel(String uid,String time,String companyId,String insuranceType,String dict,
                                                      String brand,String type) throws Exception{
        //1、获取公司信息 用于生成表头
        List<Map<String,Object>> insuranceCompanys=companyService.getCompanys();
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("报表统计--保费分配统计");
        XSSFCellStyle cellStyle = wb.createCellStyle();
        //生成表头
        this.getShetTableHead(sheet,cellStyle,wb,insuranceCompanys,type);
        Map map=null;
        //根据当周 或  当月获得查询数据
        if(Constant.STATSTICS_TYPE.week.name().equals(type)){
            map=weekInsurancePage(uid,time,companyId,insuranceType,dict,brand,"0","99999");
        }
        if(Constant.STATSTICS_TYPE.month.name().equals(type)){
            map=monthInsurancePage(uid,time,companyId,insuranceType,dict,brand,"0","99999");
        }
        if(map!=null) {
            List<Map<String, Object>> bcdList = (List<Map<String, Object>>) map.get("dataList");
            if(bcdList.size()>0) {
                for (int i = 0; i < bcdList.size(); i++) {
                    Map infoMap = bcdList.get(i);
                    Row row = sheet.createRow(i + 3);
                    row.createCell(0).setCellValue(StringUtil.formatEmpty(infoMap.get("brand")));
                    row.createCell(1).setCellValue(StringUtil.formatEmpty(infoMap.get("disp")));
                    row.createCell(2).setCellValue(StringUtil.formatEmpty(infoMap.get("oName")));
                    Integer startHj=3;
                    Integer startMonth = 0;
                    if(Constant.STATSTICS_TYPE.week.name().equals(type)) {
                        int startweek = 3;
                        for (int w = 0; w < insuranceCompanys.size(); w++) {
                            Map companyInfo = insuranceCompanys.get(w);
                            String id = StringUtil.formatEmpty(companyInfo.get("id"));
                            String countId = "week_" + id + "_amount";
                            startweek = startweek + (w * 2);
                            row.createCell(startweek).setCellValue(StringUtil.formatEmpty(infoMap.get(countId)));
                            String weekPrice = "week_" + id + "_price";
                            row.createCell(startweek + 1).setCellValue(StringUtil.formatEmpty(infoMap.get(weekPrice)));
                        }
                        startHj= startweek + 2;
                        row.createCell(startHj).setCellValue(StringUtil.formatEmpty(infoMap.get("week_amount")));
                        row.createCell(startHj + 1).setCellValue(StringUtil.formatEmpty(infoMap.get("week_price")));
                        startMonth=new Integer(startHj + 2);
                    }
                    if(Constant.STATSTICS_TYPE.month.name().equals(type)){
                        startMonth=startHj;
                    }
                    for (int w = 0; w < insuranceCompanys.size(); w++) {
                        Map companyInfo = insuranceCompanys.get(w);
                        String id = StringUtil.formatEmpty(companyInfo.get("id"));
                        String countId = "month_" + id + "_amount";
                        startMonth = new Integer(startMonth + (w * 2));
                        row.createCell(startMonth).setCellValue(StringUtil.formatEmpty(infoMap.get(countId)));
                        String weekPrice = "month_" + id + "_price";
                        row.createCell(startMonth + 1).setCellValue(StringUtil.formatEmpty(infoMap.get(weekPrice)));
                    }

                    Integer startMonthHj = startMonth + 2;
                    row.createCell(startMonthHj).setCellValue(StringUtil.formatEmpty(infoMap.get("month_amount")));
                    row.createCell(startMonthHj + 1).setCellValue(StringUtil.formatEmpty(infoMap.get("month_price")));
                }
            }
        }
        return wb;
    }



    /**
     * 根据条件先将品牌查询出
     * @param uid
     * @param time
     * @param companyId
     * @param insuranceType
     * @param disp
     * @param brand
     * @return
     */
    private Map getBrandAndCompanyAndDisp(String uid,String time,String companyId,String insuranceType,String disp,
                                          String brand,String start,String limit,String type) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        sql.append("select t.*,b.b_name as brandName from (select o_name oName,o_car_brand brand,t_disp disp,company_id,t_value" +
                " from t_insurance_order_statistics ");
        sql.append(" where 1=1 ");
        this.dayAndWeekQuaryParams(uid,sql,params,companyId,time,brand,disp,type,insuranceType);
        sql.append(" group by company_id,o_car_brand,t_value)t left join t_bc_brand b ON t.brand=b.b_code");
        return this.orderDao.page(sql.toString(),params.toArray(),start,limit);
    }

    /**
     * 获得指定时间内 指定品牌保险公司的保险个数 及保费信息
     * @param uid
     * @param time
     * @param companyId
     * @param insuranceType
     * @param disp
     * @param brand
     * @return
     * @throws Exception
     */
    public List<Map<String,Object>> getInsuranceCompanyNumsByWeek(String uid,String time,String companyId,String insuranceType,
                                                            String disp,String brand,String type) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        sql.append("select temp.*,ROUND(temp.dJqPriceVVU-temp.dJVVUqPrice,2) dJqPrice " +
                " from (select o_name oName,o_car_brand brand,t_disp disp,company_id,t_value,insurance_company_name," +
                " COUNT(if(insurance_type='0',true,null)) amount," +
                " COUNT(if(insurance_type='1',true,null)) addOrderCount," +
                " ROUND(SUM(if(insurance_type='1',sy_price+jq_price,'0')),2) addOrderPrice," +
                " COUNT(if(insurance_type='2',true,null)) backOrderCount," +
                " ROUND(SUM(if(insurance_type='2',sy_price+jq_price,'0')),2) backOrderPrice," +
                " COUNT(IF (insurance_type='0' and iscompulsoryinsurance = '0',TRUE,NULL)) dJqAmount," +
                " ROUND(SUM(if(insurance_type='0',jq_price,'0' )), 2) jqPrice," +
                " ROUND(SUM(if(insurance_type='0',sy_price,'0')), 2) syPrice," +
                " ROUND(SUM(if(insurance_type='0',jq_price * 1 + sy_price * 1,0)),2) allPrice," +
                " ROUND(SUM(IF(insurance_type='0' AND iscompulsoryinsurance = '0',jq_price * 1,0)),2) dJqPriceVVU," +
                " ROUND(SUM(IF(insurance_type='0' AND iscompulsoryinsurance = '0',items.item_price * 1,0)),2) dJVVUqPrice," +
                " ROUND(SUM(if(insurance_type='0',items.item_price,0)),2) VVULPT,"+
                " insurance_company_id " +
                " from " +
                " t_insurance_order_statistics s,(SELECT if(item_id='0',item_price,0) item_price,quotation_id " +
                " FROM t_insurance_quotation_item WHERE company_id=? group by quotation_id ) items "+
                "");
        params.add(companyId);
        sql.append(" where s.quotation_id=items.quotation_id ");
        this.dayAndWeekQuaryParams(uid,sql,params,companyId,time,brand,disp,type,insuranceType);
        sql.append(" group by company_id,o_car_brand,t_value,insurance_company_id) temp ");
        return this.orderDao.list(sql.toString(),params.toArray());
    }


    /**
     * 月度保费分配统计
     * @param uid
     * @param time
     * @param companyId
     * @param insuranceType
     * @param dict
     * @param brand
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map<String,Object> monthInsurancePage(String uid,String time,String companyId,String insuranceType,String dict,String brand,
                                                String start,String limit) throws Exception {
        time=DateUtils.monthSToStrTime(time);
        //1、根据统计信息表 根据条件查询出品牌、区域、4s店信息
        Map map=this.getBrandAndCompanyAndDisp(uid,time,companyId,insuranceType,dict,brand,start,limit,Constant.STATSTICS_TYPE.month.name());
        List<Map<String,Object>> bcdList= (List<Map<String, Object>>) map.get("dataList");
        //2、计算指定日期内的每个保险公司的保险个数及保费信息
        List<Map<String,Object>> insuranceCompanyMonthList=this.getInsuranceCompanyNumsByWeek(uid,time,companyId,insuranceType,dict,brand,Constant.STATSTICS_TYPE.month.name());
        //3、遍历统计品牌、区域、4s店信息
        for(int i=0;i<bcdList.size();i++) {
            //品牌 区域 4s店
            Map bcdMap = bcdList.get(i);
            monthInsuranceList(bcdMap,insuranceCompanyMonthList);
        }
        //增加年度保单数量
        return map;
    }


    private void monthInsuranceList(Map<String,Object> bcdMap,List<Map<String,Object>> insuranceCompanyMonthList) {
        //取出统计的品牌、区域、4s店信息
        String brandBcd = StringUtil.formatEmpty(bcdMap.get("brand"));
        String oNameBcd = StringUtil.formatEmpty(bcdMap.get("oName"));
        String dispBcd = StringUtil.formatEmpty(bcdMap.get("disp"));
        //5、遍历当月信息
        Float monthPrice=0f;
        int monthAmount=0;
        Float monthJqPrice=0f;
        int monthJqAmount=0;
        int addAmount=0;
        Float addPrice=0f;
        int backAmount=0;
        Float backPrice=0f;
        Float monthVvulpt=0f;
        for(Map monthMap:insuranceCompanyMonthList){
            String brandInc= StringUtil.formatEmpty(monthMap.get("brand"));
            String oNameInc= StringUtil.formatEmpty(monthMap.get("oName"));
            String dispInc= StringUtil.formatEmpty(monthMap.get("disp"));
            if(brandBcd.equals(brandInc) && oNameBcd.equals(oNameInc) && dispBcd.equals(dispInc)){
                String id=StringUtil.formatEmpty(monthMap.get("insuranceCompanyId"));
                String allPrice=StringUtil.formatEmpty(monthMap.get("allPrice"));
                Float allPriceNVVPrice=0f;
                if(!"".equals(allPrice)){
                    monthPrice+=Float.valueOf(allPrice);
                    allPriceNVVPrice=Float.valueOf(allPrice);
                }
                String amount=StringUtil.formatEmpty(monthMap.get("amount"));
                if(!"".equals(amount)){
                    monthAmount+=Integer.valueOf(amount);
                }
                bcdMap.put("month_"+id+"_amount",amount);
                bcdMap.put("month_"+id+"_price",allPrice);
                String dJqPrice=StringUtil.formatEmpty(monthMap.get("dJqPrice"));
                if(!"".equals(dJqPrice)){
                    monthJqPrice+=Float.valueOf(dJqPrice);
                }
                String dJqAmount=StringUtil.formatEmpty(monthMap.get("dJqAmount"));
                if(!"".equals(dJqAmount)){
                    monthJqAmount+=Integer.valueOf(dJqAmount);
                }
                bcdMap.put("month_"+id+"_jqPrice",dJqPrice);
                bcdMap.put("month_"+id+"_jqAmount",dJqAmount);
                String addOrderAmount=StringUtil.formatEmpty(monthMap.get("addOrderCount"));
                if(!"".endsWith(addOrderAmount)){
                    addAmount+=Integer.valueOf(addOrderAmount);
                }
                String addOrderPrice=StringUtil.formatEmpty(monthMap.get("addOrderPrice"));
                if(!"".endsWith(addOrderPrice)){
                    addPrice+=Float.valueOf(addOrderPrice);
                }
                String backOrderAmount=StringUtil.formatEmpty(monthMap.get("backOrderCount"));
                if(!"".endsWith(backOrderAmount)){
                    backAmount+=Integer.valueOf(backOrderAmount);
                }
                String backOrderPrice=StringUtil.formatEmpty(monthMap.get("backOrderPrice"));
                if(!"".endsWith(backOrderPrice)){
                    backPrice+=Float.valueOf(backOrderPrice);
                }
                String VVULPT=StringUtil.formatEmpty(monthMap.get("VVULPT"));
                if(!"".equals(VVULPT)){
                    monthVvulpt+=Float.valueOf(VVULPT);
                    allPriceNVVPrice=Constant.formatFloat(allPriceNVVPrice-Float.valueOf(VVULPT));
                }
                bcdMap.put("month_"+id+"_addAmount",addOrderAmount);
                bcdMap.put("month_"+id+"_addPrice",addOrderPrice);
                bcdMap.put("month_"+id+"_backAmount",backOrderAmount);
                bcdMap.put("month_"+id+"_backPrice",backOrderPrice);
                bcdMap.put("month_"+id+"_vvulptPrice",VVULPT);
                bcdMap.put("month_"+id+"_priceNVVPrice",allPriceNVVPrice);
                String dJVVUqPrice=StringUtil.formatEmpty(monthMap.get("dJVVUqPrice"));
                bcdMap.put("month_"+id+"_dJVVUqPrice",dJVVUqPrice);
            }
            bcdMap.put("month_amount",monthAmount);
            bcdMap.put("month_price",monthPrice);
            bcdMap.put("month_jqAmount",monthJqAmount);
            bcdMap.put("month_jqPrice",monthJqPrice);

            bcdMap.put("month_addAmount",addAmount);
            bcdMap.put("month_addPrice",addPrice);
            bcdMap.put("month_backAmount",backAmount);
            bcdMap.put("month_backPrice",backPrice);
            bcdMap.put("month_vvulptPrice",monthVvulpt);
            bcdMap.put("month_nVVupltprice",Constant.formatFloat(monthPrice-monthVvulpt));
        }

    }


    /**
     * 月度统计
     * @param uid
     * @param companyid
     * @param time
     * @param brand
     * @param dict
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map monthInsuranceStatics(String uid,String companyid,String time,String brand,String dict,String start,String limit) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        if("".equals(time)){
            time=DateUtils.getTimeStr();
        }
        sql.append("select * from ( ");
        sql.append("SELECT  bcd.*,sb.sbAmount,sb.sbAllprice,sb.sbAllcommissionPrice,sb.sbAllbackPrice,sb.sbQAmount,sb.sbCouponPrice," +
                " xb.xbAmount,xb.xbQAmount,xb.xbAllprice,xb.xbAllcommissionPrice,xb.xbAllbackPrice,xb.xbCouponPrice " +
                " FROM ( " +
                " SELECT  o_car_brand car_brand, company_id, o_name, t_disp disp," +
                " t_value FROM t_insurance_order_statistics ");
        sql.append(" where insurance_type='0' ");
        if(!"".equals(companyid)){
            sql.append(" and company_id=? ");
            params.add(companyid);
        }else{
            Constant.setQueryByRoleDate(uid,sql,params,"");
        }
        sql.append(" GROUP BY o_car_brand,company_id,t_disp) bcd " +
                " LEFT JOIN (" +
                " select n.*,q.sbAmount sbQAmount from (" +
                " SELECT o_car_brand car_brand,company_id,o_name,t_disp," +
                "t_value,COUNT(id) sbAmount,ROUND(SUM(jq_price + sy_price), 2) sbAllprice," +
                "   ROUND(SUM(jq_commission_price + sy_commission_price),2) sbAllcommissionPrice," +
                "   ROUND(SUM(back_Price), 2) sbAllbackPrice,   ROUND(SUM(coupon_price),2) sbCouponPrice " +
                " FROM " +
                "  t_insurance_order_statistics " +
                " WHERE  insurance_type='0' ");
        this.dayAndWeekQuaryParams(uid,sql,params,companyid,time,brand,dict,Constant.STATSTICS_TYPE.month.name(),"sb");
        sql.append(" GROUP BY o_car_brand, company_id,  t_disp) n" +
                " left join  " +
                " ( SELECT o_car_brand car_brand,company_id,o_name,t_disp disp," +
                "t_value,COUNT(id) sbAmount,ROUND(SUM(jq_price + sy_price), 2) sbAllprice, ROUND(SUM(jq_commission_price + sy_commission_price),2) sbAllcommissionPrice,ROUND(SUM(back_Price), 2) sbAllbackPrice FROM" +
                " t_insurance_order_statistics  " +
                " WHERE insurance_type='0' ");
        this.dayAndWeekQuaryParams(uid,sql,params,companyid,time,brand,dict,Constant.STATSTICS_TYPE.beforeyear.name(),"sb");
        sql.append(" GROUP BY o_car_brand,company_id,t_disp " +
                " )q  on n.car_brand=q.car_brand and n.company_id=q.company_id and n.t_value=q.t_value " +
                " ) sb ON bcd.car_brand = sb.car_brand  AND bcd.company_id = sb.company_id  AND bcd.t_value = sb.t_value " +
                "LEFT JOIN ( " +
                " select n.*,q.xbAmount xbQAmount " +
                " from( " +
                " SELECT o_car_brand car_brand,company_id,o_name,t_disp disp," +
                "t_value,COUNT(id) xbAmount,ROUND(SUM(jq_price + sy_price), 2) xbAllprice," +
                " ROUND(SUM(jq_commission_price + sy_commission_price),2) xbAllcommissionPrice,ROUND(SUM(back_Price), 2) xbAllbackPrice," +
                " ROUND(SUM(coupon_price),2) xbCouponPrice  " +
                " FROM " +
                "  t_insurance_order_statistics " +
                " WHERE insurance_type='0' ");
        this.dayAndWeekQuaryParams(uid,sql,params,companyid,time,brand,dict,Constant.STATSTICS_TYPE.month.name(),"xb");
        sql.append(" GROUP BY  o_car_brand, company_id,t_disp " +
                " )n " +
                " left join ( " +
                " SELECT o_car_brand car_brand,company_id,o_name,t_disp disp," +
                " t_value,COUNT(id) xbAmount,ROUND(SUM(jq_price + sy_price), 2) xbAllprice," +
                " ROUND(SUM(jq_commission_price + sy_commission_price),2) xbAllcommissionPrice,ROUND(SUM(back_Price), 2) xbAllbackPrice " +
                " FROM " +
                "  t_insurance_order_statistics " +
                " WHERE insurance_type='0' ");
        this.dayAndWeekQuaryParams(uid,sql,params,companyid,time,brand,dict,Constant.STATSTICS_TYPE.beforeyear.name(),"xb");
        sql.append(" GROUP BY  o_car_brand,company_id,t_disp)q " +
                " on n.company_id=q.company_id and n.car_brand=q.car_brand and n.t_value=q.t_value " +
                " ) xb ON bcd.car_brand = xb.car_brand " +
                " AND bcd.company_id = xb.company_id " +
                " AND bcd.t_value = xb.t_value ");
        sql.append(") temp ");
        return this.orderDao.page(sql.toString(),params.toArray(),start,limit);
    }


    /**
     * 保险返利台账查询
     * @param uid
     * @param start
     * @param limit
     * @param time
     * @return
     * @throws Exception
     */
    @Override
    public Map insruanceRebatPage(String uid,String start,String limit,String time,String companyId) throws Exception {
        if("".equals(time)){
            time=DateUtils.getTimeStr();
        }
        if("".equals(companyId)){
            companyId=TokenUtil.getCompanyId(uid);
        }
        StringBuffer sql=new StringBuffer();
        sql.append("SELECT m.*, c.* FROM " +
                " (SELECT  * FROM " +
                " t_insurance_order_statstics_rebate " +
                " WHERE " +
                " DATE_FORMAT(statistics_time, '%Y%m') = DATE_FORMAT(?, '%Y%m') ");
        sql.append(" AND company_id=? ");
        sql.append(" GROUP BY company_id  ) m " +
                " LEFT JOIN ( " +
                " SELECT ROUND( sum(sb_allCommission_price), 2 ) s_sb_allCommission_price, " +
                " ROUND(sum(sb_all_price), 2) s_sb_all_price, ROUND(sum(sb_back_price), 2) s_sb_back_price," +
                " ROUND( sum(sb_jqCommission_price), 2 ) s_sb_jqCommission_price, ROUND(sum(sb_jq_price), 2) s_sb_jq_price," +
                " ROUND( sum(sb_syCommission_price), 2 ) s_sb_syCommission_price, ROUND(sum(sb_sy_price), 2) s_sb_sy_price," +
                " ROUND( sum(xb_allCommission_price), 2 ) s_xb_allCommission_price, ROUND(sum(xb_all_price), 2) s_xb_all_price," +
                " ROUND(sum(xb_back_price), 2) s_xb_back_price, ROUND( sum(xb_jqCommission_price), 2 ) s_xb_jqCommission_price," +
                " ROUND(sum(xb_jq_price), 2) s_xb_jq_price, ROUND( sum(xb_syCommission_price), 2 ) s_xb_syCommission_price," +
                " ROUND(sum(xb_sy_price), 2) s_xb_sy_price, company_id," +
                " ROUND(sum(sb_all_coupon_price),2) s_sb_all_coupon_price, " +
                " ROUND(sum(xb_all_coupon_price),2) s_xb_all_coupon_price " +
                " FROM " +
                " t_insurance_order_statstics_rebate " );

        sql.append(" GROUP BY  company_id " +
                ") c ON m.company_id = c.company_id");
        System.out.println(sql.toString()+"-----------------"+companyId);
        return this.orderDao.page(sql.toString(),new String[]{time,companyId},start,limit);
    }

    /**
     * 保险返利台账导出Excel
     * @param uid
     * @param time
     * @return
     * @throws Exception
     */
    @Override
    public XSSFWorkbook insruanceRebatExpExcel(String uid,String time,String companyId) throws Exception {
        XSSFWorkbook wb = new XSSFWorkbook();
        if("".equals(companyId)){
            companyId=TokenUtil.getCompanyId(uid);
        }
        XSSFSheet sheet = wb.createSheet("报表统计--返利台账报表");
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

        XSSFRow row = sheet.createRow((int) 0);
        String[] excelHeaderRecordout = {"部门","新保优惠","续保优惠","新保差价","续保差价","新保代理费","续保代理费","新保税金",
                "续保税金","新保个税","续保个税","新保利润","续保利润","累计新保优惠","累计续保优惠","累计新保差价","累计续保差价",
                "累计新保代理费","累计续保代理费","累计新保税金","累计续保税金","累计新保个税","累计续保个税","累计新保利润","累计续保利润"};
        for (int i = 0; i < excelHeaderRecordout.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderRecordout[i]);
            cell.setCellStyle(cellStyle);
            sheet.autoSizeColumn(i);
            sheet.setDefaultRowHeightInPoints(24);
            sheet.setColumnWidth(cell.getColumnIndex(), 100 * 30);
        }
        Map map=this.insruanceRebatPage(uid,"0","99999",time,companyId);
        if(map!=null){
            List<Map<String,Object>> mapList= (List<Map<String, Object>>) map.get("dataList");
            if(mapList.size()>0){
                for (int i=0;i<mapList.size();i++) {
                    Map infoMap = mapList.get(i);
                    row = sheet.createRow(i + 1);
                    row.createCell(0).setCellValue(StringUtil.formatEmpty(infoMap.get("oName")));
                    Float sbBackPrice=0f;
                    Float sbAllCommissionPrice=0f;
                    if(!"".equals(StringUtil.formatEmpty(infoMap.get("sbBackPrice")))){
                        sbBackPrice=Float.valueOf(StringUtil.formatEmpty(infoMap.get("sbBackPrice")));
                    }
                    if(!"".equals(StringUtil.formatEmpty(infoMap.get("sbAllCommissionPrice")))){
                        sbAllCommissionPrice=Float.valueOf(StringUtil.formatEmpty(infoMap.get("sbAllCommissionPrice")));
                    }
                    row.createCell(1).setCellValue(sbBackPrice);
                    Float xbBackPrice=0f;
                    if(!"".equals(StringUtil.formatEmpty(infoMap.get("xbBackPrice")))){
                        xbBackPrice=Float.valueOf(StringUtil.formatEmpty(infoMap.get("xbBackPrice")));
                    }
                    Float xbAllCommissionPrice=0f;
                    if(!"".equals(StringUtil.formatEmpty(infoMap.get("xbAllCommissionPrice")))){
                        xbAllCommissionPrice=Float.valueOf(StringUtil.formatEmpty(infoMap.get("xbAllCommissionPrice")));
                    }
                    row.createCell(2).setCellValue(Constant.formatFloat(xbBackPrice).toString());
                    row.createCell(3).setCellValue(Constant.formatFloat(sbAllCommissionPrice-sbBackPrice).toString());//新保差价
                    row.createCell(4).setCellValue(Constant.formatFloat(xbAllCommissionPrice-xbBackPrice).toString());//续保差价
                    row.createCell(5).setCellValue(Constant.formatFloat(sbAllCommissionPrice).toString());
                    row.createCell(6).setCellValue(Constant.formatFloat(xbAllCommissionPrice).toString());
                    row.createCell(7).setCellValue("");//新保税金
                    row.createCell(8).setCellValue("");//续保税金
                    row.createCell(9).setCellValue("");//新保个税
                    row.createCell(10).setCellValue("");//续保个税
                    row.createCell(11).setCellValue("");//新保利润
                    row.createCell(12).setCellValue("");//续保利润
                    Float sSbBackPrice=0f;
                    if(!"".equals(StringUtil.formatEmpty(infoMap.get("sSbBackPrice")))){
                        sSbBackPrice=Float.valueOf(StringUtil.formatEmpty(infoMap.get("sSbBackPrice")));
                    }
                    Float sXbBackPrice=0f;
                    if(!"".equals(StringUtil.formatEmpty(infoMap.get("sXbBackPrice")))){
                        sXbBackPrice=Float.valueOf(StringUtil.formatEmpty(infoMap.get("sXbBackPrice")));
                    }
                    Float sSbAllCommissionPrice=0f;
                    if(!"".equals(StringUtil.formatEmpty(infoMap.get("sSbAllCommissionPrice")))){
                        sSbAllCommissionPrice=Float.valueOf(StringUtil.formatEmpty(infoMap.get("sSbAllCommissionPrice")));
                    }
                    Float sXbAllCommissionPrice=0f;
                    if(!"".equals(StringUtil.formatEmpty(infoMap.get("sXbAllCommissionPrice")))){
                        sXbAllCommissionPrice=Float.valueOf(StringUtil.formatEmpty(infoMap.get("sXbAllCommissionPrice")));
                    }
                    row.createCell(13).setCellValue(sSbBackPrice);
                    row.createCell(14).setCellValue(sXbBackPrice);
                    row.createCell(15).setCellValue(Constant.formatFloat(sSbAllCommissionPrice-sSbBackPrice).toString());//累计新保差价
                    row.createCell(16).setCellValue(Constant.formatFloat(sXbAllCommissionPrice-sXbBackPrice).toString());//累计续保差价
                    row.createCell(17).setCellValue(Constant.formatFloat(sSbAllCommissionPrice).toString());
                    row.createCell(18).setCellValue(Constant.formatFloat(sXbAllCommissionPrice).toString());
                    row.createCell(19).setCellValue("");//累计新保税金
                    row.createCell(20).setCellValue("");//累计续保税金
                    row.createCell(21).setCellValue("");//累计新保个税
                    row.createCell(22).setCellValue("");//累计续保个税
                    row.createCell(23).setCellValue("");//累计新保利润
                    row.createCell(24).setCellValue("");//累计续保利润
                }
            }
        }
        return wb;
    }

    /**
     * 月报表导出Excel
     * @param uid
     * @param time
     * @return
     * @throws Exception
     */
    @Override
    public XSSFWorkbook monthInsuranceStaticsExpExcel(String uid,String companyid,String time,String brand,String dict) throws Exception {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("报表统计--月统计报表");
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        //合并单元格
        CellRangeAddress cell1=new CellRangeAddress(0,1,0,0);//品牌
        setRegionBorder(cell1,sheet,wb);
        sheet.addMergedRegion(cell1);
        CellRangeAddress cell2=new CellRangeAddress(0,1,1,1);//区域
        setRegionBorder(cell2,sheet,wb);
        sheet.addMergedRegion(cell2);
        CellRangeAddress cell3=new CellRangeAddress(0,1,2,2);//4s店
        setRegionBorder(cell3,sheet,wb);
        sheet.addMergedRegion(cell3);
        CellRangeAddress cell4=new CellRangeAddress(0,0,3,10);//4s店
        setRegionBorder(cell4,sheet,wb);
        sheet.addMergedRegion(cell4);
        CellRangeAddress cell5=new CellRangeAddress(0,0,11,18);//4s店
        setRegionBorder(cell5,sheet,wb);
        sheet.addMergedRegion(cell5);
        Row row1 = sheet.createRow(0);
        Cell headCell=row1.createCell(0);
        headCell.setCellValue("品牌");
        headCell.setCellStyle(cellStyle);

        headCell=row1.createCell(1);
        headCell.setCellValue("区域");
        headCell.setCellStyle(cellStyle);

        headCell=row1.createCell(2);
        headCell.setCellValue("4S店");
        headCell.setCellStyle(cellStyle);

        headCell=row1.createCell(3);
        headCell.setCellValue("新保");
        headCell.setCellStyle(cellStyle);

        headCell=row1.createCell(11);
        headCell.setCellValue("续保");
        headCell.setCellStyle(cellStyle);

        XSSFRow row = sheet.createRow((int) 1);
        String[] excelHeaderRecordout = {"新保个数", "上年同期","同比", "新保保费规模","新保代理费", "新保优惠", "新保赠送成本","新保毛利",
                "续保个数", "上年同期","同比", "续保保费规模","续保代理费", "续保优惠", "续保赠送成本","续保毛利"};
        for (int i = 0; i < excelHeaderRecordout.length; i++) {
            XSSFCell cell = row.createCell(i+3);
            cell.setCellValue(excelHeaderRecordout[i]);
            cell.setCellStyle(cellStyle);
            sheet.autoSizeColumn(i+3);
            sheet.setDefaultRowHeightInPoints(24);
            sheet.setColumnWidth(cell.getColumnIndex(), 100 * 30);
        }
        Map map=monthInsuranceStatics(uid,companyid,time,brand,dict,"0","99999");
        if(map!=null){
            List<Map<String,Object>> mapList= (List<Map<String, Object>>) map.get("dataList");
            if(mapList.size()>0){
                for (int i=0;i<mapList.size();i++) {
                    Map infoMap=mapList.get(i);
                    row = sheet.createRow(i + 2);
                    row.createCell(0).setCellValue(StringUtil.formatEmpty(infoMap.get("carBrand")));
                    row.createCell(1).setCellValue(StringUtil.formatEmpty(infoMap.get("disp")));
                    row.createCell(2).setCellValue(StringUtil.formatEmpty(infoMap.get("oName")));
                    row.createCell(3).setCellValue(StringUtil.formatEmpty(infoMap.get("sbAmount")));
                    row.createCell(4).setCellValue(StringUtil.formatEmpty(infoMap.get("sbQAmount")));
                    row.createCell(2).setCellValue("");//同比
                    row.createCell(6).setCellValue(StringUtil.formatEmpty(infoMap.get("sbAllprice")));
                    row.createCell(7).setCellValue(StringUtil.formatEmpty(infoMap.get("sbAllcommissionPrice")));
                    row.createCell(8).setCellValue(StringUtil.formatEmpty(infoMap.get("sbAllbackPrice")));
                    row.createCell(9).setCellValue(StringUtil.formatEmpty(infoMap.get("sbCouponPrice")));
                    row.createCell(2).setCellValue("");//毛利
                    row.createCell(11).setCellValue(StringUtil.formatEmpty(infoMap.get("xbAmount")));
                    row.createCell(12).setCellValue(StringUtil.formatEmpty(infoMap.get("xbQAmount")));
                    row.createCell(2).setCellValue("");//同比
                    row.createCell(14).setCellValue(StringUtil.formatEmpty(infoMap.get("xbAllprice")));
                    row.createCell(15).setCellValue(StringUtil.formatEmpty(infoMap.get("xbAllcommissionPrice")));
                    row.createCell(16).setCellValue(StringUtil.formatEmpty(infoMap.get("xbAllbackPrice")));
                    row.createCell(17).setCellValue(StringUtil.formatEmpty(infoMap.get("xbCouponPrice")));
                    row.createCell(2).setCellValue("");//毛利

                }
            }
        }
        return wb;
    }

    /**
     * 月、周保费分配导出Excel设置表头
     * @param sheet
     * @param cellStyle
     * @param wb
     * @param insuranceCompanys
     * @param type
     */
    private void getShetTableHead(XSSFSheet sheet,XSSFCellStyle cellStyle,XSSFWorkbook wb,List<Map<String,Object>> insuranceCompanys,String type){
        int companySize=insuranceCompanys.size()*2;
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

        //合并单元格
        CellRangeAddress cell1=new CellRangeAddress(0,2,0,0);
        setRegionBorder(cell1,sheet,wb);
        CellRangeAddress cell2=new CellRangeAddress(0,2,1,1);
        setRegionBorder(cell2,sheet,wb);
        CellRangeAddress cell3=new CellRangeAddress(0,2,2,2);
        setRegionBorder(cell3,sheet,wb);
        //当周
        int weekStartCol=3;
        int weekStopCol=weekStartCol;
        //当月
        int monthStartCol=0;

        Row row1 = sheet.createRow(0);
        Row row2 = sheet.createRow(1);
        Row row3 = sheet.createRow(2);
        //当周报头
        if(Constant.STATSTICS_TYPE.week.name().equals(type)) {
            weekStopCol+=companySize;
            CellRangeAddress cell4 = new CellRangeAddress(0, 0, weekStartCol, weekStopCol - 1);//当周
            for (int i = 0; i < insuranceCompanys.size(); i++) {
                Map map = insuranceCompanys.get(i);
                weekStartCol = weekStartCol + (i * 2);
                CellRangeAddress cell_w_c = new CellRangeAddress(1, 1, weekStartCol, weekStartCol + 1);//当周 公司设置
                setRegionBorder(cell_w_c,sheet,wb);
                Cell headCell = row2.createCell(weekStartCol);
                headCell.setCellValue(StringUtil.formatEmpty(map.get("insuranceCompanyName")));
                headCell.setCellStyle(cellStyle);
                sheet.addMergedRegion(cell_w_c);

                CellRangeAddress cell_w_count = new CellRangeAddress(2, 2, weekStartCol, weekStartCol);//当周 公司个数
                setRegionBorder(cell_w_count,sheet,wb);
                Cell headCell3 = row3.createCell(weekStartCol);
                headCell3.setCellValue("个数");
                headCell3.setCellStyle(cellStyle);
                sheet.addMergedRegion(cell_w_count);
                CellRangeAddress cell_w_price = new CellRangeAddress(2, 2, weekStartCol + 1, weekStartCol + 1);//当周 公司 保费
                setRegionBorder(cell_w_price,sheet,wb);
                headCell3 = row3.createCell(weekStartCol + 1);
                headCell3.setCellValue("保费");
                headCell3.setCellStyle(cellStyle);
                sheet.addMergedRegion(cell_w_price);

            }
            CellRangeAddress cell5 = new CellRangeAddress(0, 0, weekStopCol, weekStopCol + 1);//当周合计
            setRegionBorder(cell5,sheet,wb);
            //小计
            CellRangeAddress cell5_xj = new CellRangeAddress(1, 1, weekStopCol, weekStopCol + 1);//当周合计
            setRegionBorder(cell5_xj,sheet,wb);
            sheet.addMergedRegion(cell5_xj);
            Cell headCellXj = row2.createCell(weekStopCol);
            headCellXj.setCellValue("小计");
            headCellXj.setCellStyle(cellStyle);
            sheet.addMergedRegion(cell5_xj);

            CellRangeAddress cell5_count = new CellRangeAddress(2, 2, weekStopCol, weekStopCol);//当周合计
            setRegionBorder(cell5_count,sheet,wb);
            Cell headCell3 = row3.createCell(weekStopCol);
            headCell3.setCellValue("个数");
            headCell3.setCellStyle(cellStyle);
            sheet.addMergedRegion(cell5_count);

            CellRangeAddress cell5_price = new CellRangeAddress(2, 2, weekStopCol + 1, weekStopCol + 1);//当周合计
            setRegionBorder(cell5_price,sheet,wb);
            headCell3 = row3.createCell(weekStopCol + 1);
            headCell3.setCellValue("保费");
            headCell3.setCellStyle(cellStyle);
            sheet.addMergedRegion(cell5_price);

            sheet.addMergedRegion(cell4);
            sheet.addMergedRegion(cell5);

            Cell headCell=row1.createCell(3);
            headCell.setCellValue("当周");
            headCell.setCellStyle(cellStyle);

            headCell=row1.createCell(weekStopCol);
            headCell.setCellValue("合计");
            headCell.setCellStyle(cellStyle);
            monthStartCol=weekStopCol+2;
        }

        if(Constant.STATSTICS_TYPE.month.name().equals(type)){
            monthStartCol=weekStartCol;
        }
        int monthStopCol=monthStartCol+companySize;
        CellRangeAddress cell6=new CellRangeAddress(0,0,monthStartCol,monthStopCol-1);//当月
        setRegionBorder(cell6,sheet,wb);
        for(int i=0;i<insuranceCompanys.size();i++){
            Map map=insuranceCompanys.get(i);
            monthStartCol=monthStartCol+(i*2);
            CellRangeAddress cell_w_c=new CellRangeAddress(1,1,monthStartCol,monthStartCol+1);//当周 公司设置
            setRegionBorder(cell_w_c,sheet,wb);

            Cell headCellM=row2.createCell(monthStartCol);
            headCellM.setCellValue(StringUtil.formatEmpty(map.get("insuranceCompanyName")));
            headCellM.setCellStyle(cellStyle);
            sheet.addMergedRegion(cell_w_c);

            CellRangeAddress cell_w_count=new CellRangeAddress(2,2,monthStartCol,monthStartCol);//当周 公司个数
            setRegionBorder(cell_w_count,sheet,wb);
            Cell headCell3M=row3.createCell(monthStartCol);
            headCell3M.setCellValue("个数");
            headCell3M.setCellStyle(cellStyle);
            sheet.addMergedRegion(cell_w_count);
            CellRangeAddress cell_w_price=new CellRangeAddress(2,2,monthStartCol+1,monthStartCol+1);//当周 公司 保费
            setRegionBorder(cell_w_price,sheet,wb);
            headCell3M=row3.createCell(monthStartCol+1);
            headCell3M.setCellValue("保费");
            headCell3M.setCellStyle(cellStyle);
            sheet.addMergedRegion(cell_w_price);

        }


        CellRangeAddress cell7=new CellRangeAddress(0,0,monthStopCol,monthStopCol+1);//当月合计
        setRegionBorder(cell7,sheet,wb);
        //小计
        CellRangeAddress cell7_xj=new CellRangeAddress(1,1,monthStopCol,monthStopCol+1);//当周合计
        sheet.addMergedRegion(cell7_xj);
        Cell headCell7Xj=row2.createCell(monthStopCol);
        headCell7Xj.setCellValue("小计");
        headCell7Xj.setCellStyle(cellStyle);

        CellRangeAddress cell7_count=new CellRangeAddress(2,2,monthStopCol,monthStopCol);//当周合计
        sheet.addMergedRegion(cell7_count);
        Cell headCell7=row3.createCell(monthStopCol);
        headCell7.setCellValue("个数");
        headCell7.setCellStyle(cellStyle);

        CellRangeAddress cell7_price=new CellRangeAddress(2,2,monthStopCol+1,monthStopCol+1);//当周合计
        sheet.addMergedRegion(cell7_price);
        headCell7=row3.createCell(monthStopCol+1);
        headCell7.setCellValue("保费");
        headCell7.setCellStyle(cellStyle);

        sheet.addMergedRegion(cell1);
        sheet.addMergedRegion(cell2);
        sheet.addMergedRegion(cell3);

        sheet.addMergedRegion(cell6);
        sheet.addMergedRegion(cell7);



        Cell headCell=row1.createCell(0);
        headCell.setCellValue("品牌");
        headCell.setCellStyle(cellStyle);

        headCell=row1.createCell(1);
        headCell.setCellValue("区域");
        headCell.setCellStyle(cellStyle);

        headCell=row1.createCell(2);
        headCell.setCellValue("4S店");
        headCell.setCellStyle(cellStyle);



        headCell=row1.createCell(monthStopCol-companySize);
        headCell.setCellValue("当月");
        headCell.setCellStyle(cellStyle);

        headCell=row1.createCell(monthStopCol);
        headCell.setCellValue("合计");
        headCell.setCellStyle(cellStyle);


    }

    private static void setRegionBorder(CellRangeAddress region, XSSFSheet sheet,XSSFWorkbook wb){
        RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN,region, sheet, wb);
        RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN,region, sheet, wb);
        RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN,region, sheet, wb);
        RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN,region, sheet, wb);

    }

    /**
     * 送修比财务占比
     * @param uid
     * @param time
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map insuranceRepairPage(String uid,String time,String companyId,String start,String limit) throws Exception {
        //获取品牌、4s店、保险公司信息
        Map map=this.insuranceRepairPageByCompanys(uid,time,companyId,start,limit);
        List<Map> dataList= (List<Map>) map.get("dataList");
        if(dataList.size()>0) {
            //获得当年新保、续保各自保费信息
            List<Map<String, Object>> sbList = this.insuranceRepairByType(uid, time,companyId, "sb", Constant.STATSTICS_TYPE.month.name());
            analysisAndEncaInsuranceRepair(dataList,sbList,"mSbPrice");
            List<Map<String, Object>> xbList = this.insuranceRepairByType(uid, time,companyId, "xb", Constant.STATSTICS_TYPE.month.name());
            analysisAndEncaInsuranceRepair(dataList,xbList,"mxbPrice");
            //获得累计新保、续保各自保费信息
            List<Map<String, Object>> sSbList = this.insuranceRepairByType(uid, time,companyId, "sb", Constant.STATSTICS_TYPE.year.name());
            analysisAndEncaInsuranceRepair(dataList,sSbList,"mSSbPrice");
            List<Map<String, Object>> sXbList = this.insuranceRepairByType(uid, time,companyId, "xb", Constant.STATSTICS_TYPE.year.name());
            analysisAndEncaInsuranceRepair(dataList,sXbList,"mSXbPrice");
            //获得去年同期新保、续保各大自保费信息
            List<Map<String, Object>> qSbList = this.insuranceRepairByType(uid, time,companyId, "sb", Constant.STATSTICS_TYPE.beforeyear.name());
            analysisAndEncaInsuranceRepair(dataList,qSbList,"qMSbPrice");
            List<Map<String, Object>> qXbList = this.insuranceRepairByType(uid, time,companyId, "xb", Constant.STATSTICS_TYPE.beforeyear.name());
            analysisAndEncaInsuranceRepair(dataList,qXbList,"qMXbPrice");
        }
        return map;
    }

    private void analysisAndEncaInsuranceRepair(List<Map> dataList,List<Map<String, Object>> insurnaceList,String byName){
        for(int i=0;i<dataList.size();i++){
            //品牌 区域 4s店
            Map dataMap=dataList.get(i);
            //取出统计的品牌、区域、4s店信息
            String brand= StringUtil.formatEmpty(dataMap.get("brand"));
            String companyName= StringUtil.formatEmpty(dataMap.get("companyName"));
            String insuranceCompanyName= StringUtil.formatEmpty(dataMap.get("insuranceCompanyName"));
            for (Map insuranceMap:insurnaceList) {
                String brandIn= StringUtil.formatEmpty(insuranceMap.get("brand"));
                String companyNameIn= StringUtil.formatEmpty(insuranceMap.get("companyName"));
                String insuranceCompanyNameIn= StringUtil.formatEmpty(insuranceMap.get("insuranceCompanyName"));
                if((brand.equals(brandIn)) && (companyName.equals(companyNameIn)) && (insuranceCompanyName.equals(insuranceCompanyNameIn))){
                    dataMap.put(byName,insuranceMap.get("price"));
                }
            }
        }
    }

    /**
     * 根据
     * @param uid
     * @param time
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    public Map insuranceRepairPageByCompanys(String uid,String time,String companyId,String start,String limit) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>(); 
        this.insuranceRepairPageByCompanysToSql(sql,time,companyId,params);
        return this.orderDao.page(sql.toString(),params.toArray(),start,limit);
    }

    public List<Map<String, Object>> insuranceRepairByType(String uid, String time,String companyId, String insuranceType,String ststicsType) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        sql.append("SELECT  companys.*, sb.allPrice price FROM  ( " );
        this.insuranceRepairPageByCompanysToSql(sql,time,companyId,params);
        sql.append(") companys " +
                " LEFT JOIN ( " +
                " SELECT  o_car_brand brand,  o_name companyName,  insurance_company_name,  " +
                " ROUND(SUM(jq_price * 1 + sy_price * 1),2) allPrice " +
                " FROM " +
                " t_insurance_order_statistics " +
                " WHERE  insure_type = ? ");
        params.add(insuranceType);
        if(ststicsType.equals(Constant.STATSTICS_TYPE.month.name())) {
            if (!"".equals(time)) {
                sql.append(" AND DATE_FORMAT(out_order_date,'%Y%m')=DATE_FORMAT(?,'%Y%m') ");
                params.add(time);
            } else {
                sql.append(" AND DATE_FORMAT(out_order_date,'%Y%m')=DATE_FORMAT(now(),'%Y%m') ");
            }
        }
        if(ststicsType.equals(Constant.STATSTICS_TYPE.beforeyear.name())){
            if (!"".equals(time)) {
                sql.append(" AND DATE_FORMAT(out_order_date,'%Y%m')=DATE_FORMAT(DATE_sub(?,INTERVAL 1 YEAR),'%Y%m') ");
                params.add(time);
            } else {
                sql.append(" AND DATE_FORMAT(out_order_date,'%Y%m')=DATE_FORMAT(DATE_sub(now(),INTERVAL 1 YEAR),'%Y%m') ");
            }
        }
        sql.append(" GROUP BY o_car_brand, company_id,insurance_company_id " +
                ") sb ON companys.brand = sb.brand " +
                "AND companys.companyName = sb.companyName " +
                "AND companys.insurance_company_name = sb.insurance_company_name");
        return this.orderDao.list(sql.toString(),params.toArray());
    }

    public void insuranceRepairPageByCompanysToSql(StringBuffer sql,String time,String companyId,List<String> params){
        sql.append(" SELECT " +
                " o_car_brand brand, " +
                " o_name companyName, " +
                " insurance_company_name " +
                " FROM " +
                " t_insurance_order_statistics ");
        sql.append(" where 1=1 ");
        sql.append(" and company_id=? ");
        params.add(companyId);
        if(!"".equals(time)){
            sql.append(" AND DATE_FORMAT(out_order_date,'%Y%m')=DATE_FORMAT(?,'%Y%m') ");
            params.add(time);
        }else{
            sql.append(" AND DATE_FORMAT(out_order_date,'%Y%m')=DATE_FORMAT(now(),'%Y%m') ");
        }

        sql.append(" GROUP BY " +
                " o_car_brand, " +
                " company_id, " +
                " insurance_company_id ");
    }

    /**
     * 统计单店保险销售信息
     * @param time
     * @param insuranceType
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String,Object>> insuranceSales(String companyId,String time,String insuranceType,String uid) throws Exception {
        if("".equals(companyId)){
            companyId=TokenUtil.getCompanyId(uid);
        }
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        sql.append("select temp.*,ROUND(temp.price-temp.VVULPT,2) nVVTPrice from (" +
                "SELECT" +
                " insurance_company_id," +
                " insurance_company_name, " +
                " ROUND(COALESCE(SUM(if(insurance_type='0',sy_price,'0') + if(insurance_type='0',jq_price,'0')),0),2) price, " +
                " ROUND(COALESCE(SUM(if(sy_commission_price is not null and insurance_type='0' ,sy_commission_price,'0') + " +
                " if(jq_commission_price is not null and insurance_type='0',jq_commission_price,'0')),0),2) commissionPrice, " +
                " ROUND(COALESCE(SUM(sy_add_commission_price),0),2) syAddCommissionPrice," +
                " ROUND(COALESCE(SUM(if(insurance_type='0',back_Price,0)),0),2) backprice, " +
                " ROUND(COALESCE(SUM(if(insurance_type='0',coupon_price,0)),0),2) couponPrice, " +
                " COUNT(if(insurance_type='0',true,null)) count," +
                " COUNT(if(insurance_type='1',true,null)) addOrderCount," +
                " ROUND(SUM(if(insurance_type='1',sy_price+jq_price,0)),2) addOrderPrice," +
                " COUNT(if(insurance_type='2',true,null)) backOrderCount," +
                " ROUND(COALESCE(SUM(if(insurance_type='2',sy_price+jq_price,0)),0),2) backOrderPrice," +
                " ROUND(COALESCE(SUM(if(insurance_type='0',distcount*1,0)),0)) distCount,  " +
                " ROUND(sum(if(insurance_type = '0',items.item_price,0))) VVULPT"+
                " FROM " +
                " t_insurance_order_statistics s,(SELECT if(item_id='0',item_price,0) item_price,quotation_id  " +
                " FROM t_insurance_quotation_item WHERE company_id=? group by quotation_id) items " +
                " WHERE s.quotation_id=items.quotation_id and company_id=? ");
        params.add(companyId);
        params.add(companyId);
        if("".equals(time)){
            sql.append(" and DATE_FORMAT(out_order_date,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d')");
        }else{
            sql.append(" and DATE_FORMAT(out_order_date,'%Y-%m-%d')=DATE_FORMAT(?,'%Y-%m-%d')");
            params.add(time);
        }
        if(!"".equals(insuranceType)){
            sql.append(" and insure_type = ? ");
            params.add(insuranceType);
        }
        sql.append("  " +
                " GROUP BY " +
                " insurance_company_id" +
                ") temp ");
        return this.orderDao.list(sql.toString(),params.toArray());
    }

    //--------------------------------------------保险月报开始------------------------------------------------------------

    /**
     * 1、统计出每个4s店内的总体及当月保险销售、车辆销售及去年周期保费规模
     * 2、计算出当月每个保险公司的出单情况 交商共保+商业险与单交强的数量及保费规模
     * 3、解析数据，合并内容
     * 
     */
    @Override
    public List<Map<String,Object>> monthStatics(String companyId,String date,String insureType) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<Object> params=new ArrayList<>();
        date=DateUtils.monthSToStrTime(date);
        String d2=DateUtils.formatLongTimeByYYYYMMDD(DateUtils.addMonthLongTime(DateUtils.dayToLongTime(date),-12));
        this.monthStaticsToSql(companyId,date,insureType,sql,params,d2);
        //1、统计每个4s店的基本信息
        List<Map<String,Object>> companyInfos=this.orderDao.list(sql.toString(),params.toArray());
        for (Map<String,Object> companyInfo:companyInfos) {
            //2、计算当月每个保险公司的出单情况
            List<Map<String,Object>> insuranceInfos=this.insuranceCompanyOrders(date,companyId,insureType);
            for(Map<String,Object> info:insuranceInfos){
                String id=StringUtil.formatEmpty(info.get("insuranceCompanyId"));
                String js="".equals(StringUtil.formatEmpty(info.get("js")))?"0":StringUtil.formatEmpty(info.get("js"));
                companyInfo.put(id+"_js",js);
                String jsPrice="".equals(StringUtil.formatEmpty(info.get("jsPrice")))?"0":StringUtil.formatEmpty(info.get("jsPrice"));
                companyInfo.put(id+"_jsPrice",jsPrice);
                String dj="".equals(StringUtil.formatEmpty(info.get("dj")))?"0":StringUtil.formatEmpty(info.get("dj"));
                companyInfo.put(id+"_dj",dj);
                String djPrice="".equals(StringUtil.formatEmpty(info.get("djPrice")))?"0":StringUtil.formatEmpty(info.get("djPrice"));
                System.out.println(djPrice +"  "+jsPrice);
                companyInfo.put(id+"_djPrice",djPrice);
            }
            //3、获得去年同期保险销售数量
            Map<String,Object> qInsuranceInfo=this.insuranceCompanyQOrders(d2,companyId,insureType);
//            if(qInsuranceInfo.size()>0){
                String js="".equals(StringUtil.formatEmpty(qInsuranceInfo.get("js")))?"0":StringUtil.formatEmpty(qInsuranceInfo.get("js"));
                companyInfo.put("js",js);
                String jsPrice="".equals(StringUtil.formatEmpty(qInsuranceInfo.get("jsPrice")))?"0":StringUtil.formatEmpty(qInsuranceInfo.get("jsPrice"));
                companyInfo.put("jsPrice",jsPrice);
                String dj="".equals(StringUtil.formatEmpty(qInsuranceInfo.get("dj")))?"0":StringUtil.formatEmpty(qInsuranceInfo.get("dj"));
                companyInfo.put("dj",dj);
                String djPrice="".equals(StringUtil.formatEmpty(qInsuranceInfo.get("djPrice")))?"0":StringUtil.formatEmpty(qInsuranceInfo.get("djPrice"));
                companyInfo.put("djPrice",djPrice);
//            }
        }
        return companyInfos;
    }
    
    private void monthStaticsToSql(String companyId,String date,String insureType,StringBuffer sql,List<Object> params,String d2) throws Exception {
        sql.append("select t.*,t0.monthOrderPrice nMonthOrderPrice,t0.monthOrderSales nMonthOrderSales," +
                " o.o_name,ROUND((monthFromCarOrderSales/monthCarSales)*100,2) monthFromcarOrderRatio," +//投保率本地
                " ROUND((monthSSales/monthCarAllSales)*100,2) monthCarOrderRatio," +
                " ROUND((orderSales/yearCarSales)*100,2) yearOrderRatio," +
                " ROUND(((t.orderSales -t0.monthOrderSales) / t0.monthOrderSales)* 100,2) as onYearBaseOrderSales, " +//同比销量
                " ROUND(((t.monthOrderPrice -  t0.monthOrderPrice) /  t0.monthOrderPrice)* 100,2) onYearBaseOrderPrice, "+//同比保费规模
                " ROUND((t.monthOrderPrice/t.monthCarAllSales)*100,2) monthOneCarOrderPrice,"+//月度单车保费
                " ROUND((t.yearOrderPrice/t.yearCarSales)*100,2) yearOneCarOrderPrice,"+//年度单车保费
                " ROUND(t.monthOrderCommissionPrice-t.monthOrderBackPrice-t.monthOrderCouponPrice,2) monthNteProfit, "+
                " ROUND(t.yearOrderCommissionPrice-t.yearOrderBackPrice-t.yearOrderCouponPrice,2) yearNteProfit, "+
                " ROUND(t.monthOrderCommissionPrice/t.monthCarOrderSales,2) monthOrderGrossProfit,"+
                " ROUND(t.yearOrderCommissionPrice/t.yearOrderPrice,2) yearOrderGrossProfit"+
                " from (" +
                "SELECT" +
                " c.company_id," +
                " COUNT(if(FROM_UNIXTIME(c.buy_date/1000,'%Y')=DATE_FORMAT('"+date+"','%Y'),true,null)) yearCarSales," +//年度总销量
                " COUNT(if(DATE_FORMAT(o.out_order_date,'%Y')=DATE_FORMAT('"+date+"','%Y') and o.iscompulsoryinsurance>0 ,true,NULL)) orderSales," +//本年车量投保数据
                " COUNT(if(DATE_FORMAT(o.out_order_date,'%Y')=DATE_FORMAT('"+date+"','%Y') and o.iscompulsoryinsurance=0 and o.continuous='false',true,NULL)) orderDjSales," +
                " COUNT(if(c.from_company='是' and FROM_UNIXTIME(c.buy_date/1000,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m'),true,NULL)) monthCarSales," +//新车本地销
                " COUNT(if(c.from_company='是' and FROM_UNIXTIME(c.buy_date/1000,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m') and o.iscompulsoryinsurance is not null,true,NULL)) monthFromCarOrderSales," +//本地车投保率
                " COUNT(if(FROM_UNIXTIME(c.buy_date/1000,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m'),true,NULL)) monthCarAllSales," +//新车总销量
                " COUNT(if(DATE_FORMAT(o.out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m') and o.iscompulsoryinsurance>0,true,NULL)) monthCarOrderSales," +
                " COUNT(if(DATE_FORMAT(o.out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m') and o.iscompulsoryinsurance>0,true,NULL)) monthSSales," +//投保率 全体车辆
                " COUNT(if(DATE_FORMAT(o.out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m') and o.iscompulsoryinsurance='0' and o.continuous='false',true,NULL)) monthJSales," +
                " ROUND(COALESCE(SUM(if(DATE_FORMAT(o.out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m'),o.sy_price+o.jq_price,0)),0),2) monthOrderPrice," +
                " ROUND(COALESCE(SUM(if(DATE_FORMAT(o.out_order_date,'%Y')=DATE_FORMAT('"+date+"','%Y'),o.sy_price+o.jq_price,0)),0),2) yearOrderPrice," +
                " ROUND(COALESCE(SUM(if(DATE_FORMAT(o.out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m'),o.sy_commission_price+o.jq_commission_price,0)),0),2) monthOrderCommissionPrice," +
                " ROUND(COALESCE(SUM(if(DATE_FORMAT(o.out_order_date,'%Y')=DATE_FORMAT('"+date+"','%Y'),o.sy_commission_price+o.jq_commission_price,0)),0),2) yearOrderCommissionPrice," +
                " ROUND(COALESCE(SUM(if(DATE_FORMAT(o.out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m'),o.back_Price,0)),0),2) monthOrderBackPrice," +
                " ROUND(COALESCE(SUM(if(DATE_FORMAT(o.out_order_date,'%Y')=DATE_FORMAT('"+date+"','%Y'),o.back_Price,0)),0),2) yearOrderBackPrice," +
                " ROUND(COALESCE(SUM(if(DATE_FORMAT(o.out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m'),o.coupon_price,0)),0),2) monthOrderCouponPrice," +
                " ROUND(COALESCE(SUM(if(DATE_FORMAT(o.out_order_date,'%Y')=DATE_FORMAT('"+date+"','%Y'),o.coupon_price,0)),0),2) yearOrderCouponPrice" +
                " FROM" +
                " t_crm_car c " +
                " left join " +
                " (select * from t_insurance_order_statistics where insure_type=? and insurance_type='0') o ON c.id=o.car_id" +
                " WHERE c.company_id = ? ");
        if (insureType.equals(String.valueOf(Constant.INSURE_TYPE.sb.ordinal()))) {
            sql.append(" AND FROM_UNIXTIME(c.buy_date/1000, '%Y') = DATE_FORMAT('"+date+"','%Y') ");
        }

        sql.append(" group by c.company_id");
        params.add(insureType);
        params.add(companyId);
        sql.append(" ) t left join (" +
                " SELECT c.company_id," +
                " COUNT(if(DATE_FORMAT(o.out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m') and o.iscompulsoryinsurance is not null and o.iscompulsoryinsurance>0 ,true,NULL)) monthOrderSales," +//去年同期
                " ROUND(COALESCE(SUM(if(DATE_FORMAT(o.out_order_date,'%Y%m')=DATE_FORMAT('"+d2+"','%Y%m'),o.sy_price+o.jq_price,0)),0),2) monthOrderPrice" +
                " FROM" +
                " t_crm_car c " +
                " left join " +
                " (select * from t_insurance_order_statistics where insure_type=?  and insurance_type='0') o ON c.id=o.car_id" +
                " WHERE" +
                " c.company_id = ? and insurance_type='0'" +
                " AND FROM_UNIXTIME(c.buy_date/1000, '%Y') = DATE_FORMAT('"+d2+"','%Y') " +
                " group by c.company_id" +
                " ) t0 ON t.company_id=t0.company_id" +
                " left join t_bc_organ o ON t.company_id=o.id");
        params.add(insureType);
        params.add(companyId);
    }

    /**
     * 本月保险销售数据
     * @param date
     * @param companyId
     * @param insureType
     * @return
     * @throws Exception
     */
    private List<Map<String,Object>> insuranceCompanyOrders(String date,String companyId,String insureType) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<Object> params=new ArrayList<>();
        sql.append("select  company_id,insurance_company_id,insurance_company_name," +
                " COUNT(if(iscompulsoryinsurance>0,true,null)) js," +
                " ROUND(COALESCE(SUM(if(iscompulsoryinsurance>0,sy_price+jq_price,0)),0),2) jsPrice," +
                " COUNT(if(iscompulsoryinsurance=0  and continuous='false',true,null)) dj," +
                " ROUND(SUM(if(iscompulsoryinsurance=0, jq_price,0)),2) djPrice" +
                " from t_insurance_order_statistics where company_id=? and insurance_type='0' " +
                " and DATE_FORMAT(out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m')" +
                " and insure_type=? " +
                "group by insurance_company_id");
        params.add(companyId);
        params.add(insureType);
        return this.orderDao.list(sql.toString(), params.toArray());
    }

    /**
     * 去年同期信息
     * @param date
     * @param companyId
     * @param insureType
     * @return
     * @throws Exception
     */
    private Map<String,Object> insuranceCompanyQOrders(String date,String companyId,String insureType) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<Object> params=new ArrayList<>();
        sql.append("select " +
                " company_id," +
                " COALESCE(COUNT(if(iscompulsoryinsurance>0,true,null)),0) js," +
                " ROUND(COALESCE(SUM(if(iscompulsoryinsurance>0,sy_price+jq_price,0)),0),2) jsPrice," +
                " COALESCE(COUNT(if(iscompulsoryinsurance=0 and continuous='false',true,null)),0) dj," +
                " ROUND(COALESCE(SUM(if(iscompulsoryinsurance=0 and continuous='false',jq_price,0)),0),2) djPrice" +
                " from t_insurance_order_statistics where company_id=? and insurance_type='0' " +
                " and insure_type=? " +
                " and DATE_FORMAT(out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m') group by company_id");
        params.add(companyId);
        params.add(insureType);
        return this.orderDao.info(sql.toString(),params.toArray());
    }

    //--------------------------------------------保险月报结束------------------------------------------------------------
    //--------------------------------------------OA台账报表开始----------------------------------------------------------

    /**
     * 1、先查询正常及追加保单，生成基本的数据信息
     * 2、查询退保信息，退保需要关联原保单信息；
     * 3、设置导出Excel表头；
     * 4、填充数据；
     * @param uid
     * @param data
     */
    @Override
    public XSSFWorkbook expInsuranceOrderToExcel(String uid, Map<String,Object> data,String type,String newPath,String fileId) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        //获得默认和追加保单
        this.expInsuranceOrderOAByDayToSql(uid,data,sql,params,true);
        List<Map<String,Object>> defAddOrderlist=this.orderDao.list(sql.toString(),params.toArray());
        //获得退单
        StringBuffer sqlBack=new StringBuffer();
        params.clear();
        sql=new StringBuffer();
        sqlBack.append("select back.*,tio.out_order_date sourceOutDate from (");
        this.expInsuranceOrderOAByDayToSql(uid,data,sql,params,false);
        sqlBack.append(sql.toString());
        sqlBack.append(") back,t_insurance_order tio where back.insurance_id=tio.id");
        List<Map<String,Object>> backOrderList=this.orderDao.list(sqlBack.toString(),params.toArray());
        //合并数据
        defAddOrderlist.addAll(backOrderList);
        XSSFWorkbook wb=null;
        switch (type){
            case "day":
                wb=insuranceOrderOAByDayExpExcel(defAddOrderlist,newPath,fileId);
                break;
            case "oa":
                wb=insuranceOrderOAExpExcel(defAddOrderlist,newPath,fileId);
                break;
            default:
                wb=insuranceOrderOAExpExcel(defAddOrderlist,newPath,fileId);
                break;
        }
        return wb;
    }


    public XSSFWorkbook insuranceOrderOAByDayExpExcel(List<Map<String,Object>> orderList,String path,String fileId) throws Exception {
        //查找文件位置xubaoribaotaizhang
        FileInputStream fs=new FileInputStream(path+ File.separator+fileId+".xlsx");  //获取d://test.xls
        //获取excel
        XSSFWorkbook wb=new XSSFWorkbook(fs);
        XSSFSheet sheet=wb.getSheetAt(0);  //获取到工作表，因为一个excel可能有多个工作表
        XSSFRow row=sheet.getRow(0);  //获取第一行（excel中的行默认从0开始，所以这就是为什么，一个excel必须有字段列头），即，字段列头，便于赋值
        FileOutputStream out=new FileOutputStream(path+ File.separator+fileId+".xlsx");  //向d://test.xls中写数据
        for (int i = 0; i < orderList.size(); i++) {
            Map<String,Object> map=orderList.get(i);
            row=sheet.createRow((short)(sheet.getLastRowNum()+1)); //在现有行号后追加数据
            row.createCell(0).setCellValue("".equals(StringUtil.formatEmpty(map.get("carLabel")))?
                    StringUtil.formatEmpty(map.get("carSeries")):StringUtil.formatEmpty(map.get("carLabel")));
            row.createCell(1).setCellValue(StringUtil.formatEmpty(map.get("plateNum")));
            row.createCell(2).setCellValue(StringUtil.formatEmpty(map.get("buyDate")));
            row.createCell(3).setCellValue(StringUtil.formatEmpty(map.get("insured")));
            String insuranceType=StringUtil.formatEmpty(map.get("insuranceType"));

            if (insuranceType.equals(String.valueOf(Constant.INSURNACE_TYPE.add.ordinal()))) {
                insuranceType="追";
            }else if (insuranceType.equals(String.valueOf(Constant.INSURNACE_TYPE.back.ordinal()))) {
                insuranceType="退"+"_"+StringUtil.formatEmpty(map.get("sourceOutDate"));
            }else if ("true".equals(StringUtil.formatEmpty(map.get("continuous")))) {
                insuranceType="联";
            }else{
                insuranceType="否";
            }
            row.createCell(4).setCellValue(insuranceType);
            if(!String.valueOf(Constant.INSURANCE_PROPERTY.jq.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                row.createCell(5).setCellValue(StringUtil.formatEmpty(map.get("insuranceCompanyName")));
                row.createCell(6).setCellValue(StringUtil.formatEmpty(map.get("syContractNo")));
                row.createCell(7).setCellValue(StringUtil.formatEmpty(map.get("outOrderDate")));
                row.createCell(8).setCellValue(StringUtil.formatEmpty(map.get("syStartDate")));
            }
            if(!String.valueOf(Constant.INSURANCE_PROPERTY.sy.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                row.createCell(9).setCellValue(StringUtil.formatEmpty(map.get("insuranceCompanyName")));
                row.createCell(10).setCellValue(StringUtil.formatEmpty(map.get("jqContractNo")));
                row.createCell(11).setCellValue(StringUtil.formatEmpty(map.get("outOrderDate")));
                row.createCell(12).setCellValue(StringUtil.formatEmpty(map.get("jqStartDate")));
            }

            Float syPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("syPrice")))?"0":StringUtil.formatEmpty(map.get("syPrice")));
            Float jqPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("jqPrice")))?"0":StringUtil.formatEmpty(map.get("jqPrice")));
            Float itemPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("itemPrice")))?"0":StringUtil.formatEmpty(map.get("itemPrice")));
            Float syAndJqPrice=syPrice+jqPrice;
                syAndJqPrice=syAndJqPrice-itemPrice;
            row.createCell(13).setCellValue(itemPrice);
            row.createCell(14).setCellValue(syPrice);
            if(!String.valueOf(Constant.INSURANCE_PROPERTY.sy.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                row.createCell(15).setCellValue(Constant.formatFloat(jqPrice-itemPrice));
            }

            row.createCell(16).setCellValue(syAndJqPrice);//合计
            row.createCell(17).setCellValue("true".equals(StringUtil.formatEmpty(map.get("syIsCommissionTemp")))?
                    StringUtil.formatEmpty(map.get("syCommissionTemp")):StringUtil.formatEmpty(map.get("syCommission")));
            row.createCell(18).setCellValue("true".equals(StringUtil.formatEmpty(map.get("jqIsCommissionTemp")))?
                    StringUtil.formatEmpty(map.get("jqCommissionTemp")):StringUtil.formatEmpty(map.get("jqCommission")));
            Float syCommissionPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("syCommissionPrice")))?"0":StringUtil.formatEmpty(map.get("syCommissionPrice")));
            Float jqCommissionPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("jqCommissionPrice")))?"0":StringUtil.formatEmpty(map.get("jqCommissionPrice")));
            row.createCell(19).setCellValue(syCommissionPrice);
            row.createCell(20).setCellValue(jqCommissionPrice);

            row.createCell(21).setCellValue(syCommissionPrice+jqCommissionPrice);
            Float backPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("backPrice")))?"0":StringUtil.formatEmpty(map.get("backPrice")));
            if(!String.valueOf(Constant.INSURANCE_PROPERTY.jq.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))){
                row.createCell(22).setCellValue(backPrice);//商业险返现
            }else{
                row.createCell(23).setCellValue(backPrice);//交强险返现
            }
            row.createCell(24).setCellValue(backPrice);//合计
            row.createCell(25).setCellValue(syPrice);
            if(!String.valueOf(Constant.INSURANCE_PROPERTY.sy.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                row.createCell(26).setCellValue(Constant.formatFloat(jqPrice-itemPrice).toString());
            }
            row.createCell(27).setCellValue(syAndJqPrice);
            if(!String.valueOf(Constant.INSURANCE_PROPERTY.jq.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                row.createCell(28).setCellValue(Constant.formatFloat((syCommissionPrice+jqCommissionPrice)-backPrice).toString());
            }
            if(String.valueOf(Constant.INSURANCE_PROPERTY.jq.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                row.createCell(29).setCellValue(Constant.formatFloat((syCommissionPrice+jqCommissionPrice)-backPrice).toString());
            }
            row.createCell(30).setCellValue(Constant.formatFloat(((syCommissionPrice+jqCommissionPrice)-backPrice)).toString());
            row.createCell(31).setCellValue(Constant.getPayType(StringUtil.formatEmpty(map.get("payType"))));
            row.createCell(32).setCellValue(StringUtil.formatEmpty(map.get("couponItmes")));
            row.createCell(33).setCellValue(StringUtil.formatEmpty(map.get("couponPrice")));
            if(!"".equals(StringUtil.formatEmpty(map.get("syAddCommission")))) {
                //非单交强的情况下
                if(!String.valueOf(Constant.INSURANCE_PROPERTY.jq.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                    Float syAddCommisionPrice=Constant.formatFloat(syPrice*((Float.valueOf(StringUtil.formatEmpty(map.get("syAddCommission"))))/100));
                    row.createCell(34).setCellValue(syAddCommisionPrice.toString());//加投
                }
            }else{
                row.createCell(34).setCellValue("");//加投
            }
            row.createCell(35).setCellValue("");//是否品牌车辆
            row.createCell(36).setCellValue("");

            row.createCell(37).setCellValue("".equals(StringUtil.formatEmpty(map.get("insuranceCommissionerName")))?
                    StringUtil.formatEmpty(map.get("uName")):StringUtil.formatEmpty(map.get("insuranceCommissionerName")));
            row.createCell(38).setCellValue(StringUtil.formatEmpty(map.get("label")));
            row.createCell(39).setCellValue("");
        }
        out.flush();
        wb.write(out);
        out.close();
        return wb;
    }

    private void expInsuranceOrderOAByDayToSql(String uid,Map<String,Object> data,StringBuffer sql,List<String> params,
                                               boolean insuranceType){
        sql.append(" SELECT tio.*,items.item_name,items.item_price,ins.label " +
                " FROM t_insurance_order_statistics tio left join (" +
                " SELECT * FROM t_insurance_quotation_item WHERE item_id = '0' ) items" +
                " on  tio.quotation_id = items.quotation_id AND tio.company_id = items.company_id " +
                " left join (select * from t_bc_newdict  where p_code='insuranceType') ins ON tio.base_type=ins.id  ");
        this.expInsuranceOrderParamsToSql(sql,data,params,uid,insuranceType);
    }

    private void expInsuranceOrderParamsToSql(StringBuffer sql,Map<String,Object> data,List<String> params,String uid,
                                              boolean insuranceType){
        if(insuranceType){
            sql.append(" where tio.insurance_type < 2 ");
        }else{
            sql.append(" where tio.insurance_type = 2 ");
        }
        if(!"".equals(StringUtil.formatEmpty(StringUtil.formatEmpty(data.get("companyId"))))){
            sql.append(" and tio.company_id=? ");
            params.add(StringUtil.formatEmpty(data.get("companyId")));
        }else {
            Constant.setQueryByRoleDate(uid, sql, params, "tio");
        }
        //筛选保险公司名称
        if (!"".equals(StringUtil.formatEmpty(StringUtil.formatEmpty(data.get("insuranceCompanyId"))))) {
            sql.append(" and tio.insurance_company_id =? ");
            params.add(StringUtil.formatEmpty(data.get("insuranceCompanyId")));
        }
        //车牌号或VIN
        if (!"".equals(StringUtil.formatEmpty(data.get("plateNum")))) {
            sql.append(" and ( tio.plate_num like ? or tio.vin like ? ) ");
            params.add("%" + data.get("plateNum") + "%");
            params.add("%" + data.get("plateNum") + "%");
        }
        //出单日期
        if (!"".equals(StringUtil.formatEmpty(data.get("outOrderStartDate")))) {
            sql.append(" and tio.out_order_date >= ?  ");
            params.add(StringUtil.formatEmpty(data.get("outOrderStartDate")));
        }
        if (data.get("outOrderStopDate") != null && !"".equals(StringUtil.formatEmpty(data.get("outOrderStopDate")))) {
            sql.append(" and tio.out_order_date <= ?  ");
            params.add(StringUtil.formatEmpty(data.get("outOrderStopDate")));
        }
        //保险专员
        if (data.get("insuranceCommissionerName") != null && !"".equals(StringUtil.formatEmpty(data.get("insuranceCommissionerName")))) {
            sql.append(" and (tio.insurance_commissioner_name like ? or tio.u_name like ?) ");
            params.add("%" + StringUtil.formatEmpty(data.get("insuranceCommissionerName")) + "%");
            params.add("%" + StringUtil.formatEmpty(data.get("insuranceCommissionerName")) + "%");
        }
        //基盘类型
        if (data.get("baseType") != null && !"".equals(StringUtil.formatEmpty(data.get("baseType")))) {
            sql.append(" and tio.base_type = ?  ");
            params.add(StringUtil.formatEmpty(data.get("baseType")));
        }
        //返现状态
        if (data.get("backType") != null && !"".equals(StringUtil.formatEmpty(data.get("backType")))) {
            sql.append(" and tio.back_type = ?  ");
            params.add(StringUtil.formatEmpty(data.get("backType")));
        }
        //车辆ID
        if(data.get("carId")!=null && !"".equals(StringUtil.formatEmpty(data.get("carId")))){
            sql.append(" and tio.car_id=? ");
            params.add(StringUtil.formatEmpty(data.get("carId")));
        }
        //报价单ID
        if(data.get("quotationId")!=null && !"".equals(StringUtil.formatEmpty(data.get("quotationId")))){
            sql.append(" and tio.quotation_id=? ");
            params.add(StringUtil.formatEmpty(data.get("quotationId")));
        }
        if(data.get("commissionType")!=null && !"".equals(StringUtil.formatEmpty(data.get("commissionType")))){
            sql.append(" and tio.commission_type=? ");
            params.add(StringUtil.formatEmpty(data.get("commissionType")));
        }
        //投保类别
        if(data.get("insureType")!=null && !"".endsWith(StringUtil.formatEmpty(data.get("insureType")))){
            sql.append(" and tio.insure_type=? ");
            params.add(StringUtil.formatEmpty(data.get("insureType")));
        }
        if(data.get("brand")!=null && !"".equals(StringUtil.formatEmpty(data.get("brand")))){
            sql.append(" and tio.o_car_brand=? ");
            params.add(StringUtil.formatEmpty(data.get("brand")));
        }
        if(data.get("area")!=null && !"".equals(StringUtil.formatEmpty(data.get("area")))){
            sql.append(" and tio.o_area=? ");
            params.add(StringUtil.formatEmpty(data.get("area")));
        }
        //保单类别 0 正常 1 追加 2 退单
        if(data.get("insuranceType")!=null && !"".equals(StringUtil.formatEmpty(data.get("insuranceType")))){
            sql.append(" and tio.insurance_type=? ");
            params.add(StringUtil.formatEmpty(data.get("insuranceType")));
        }
        //是否单交强  0单交强 1单商业 2交强 商业
        if(data.get("iscompulsoryinsurance")!=null && !"".equals(StringUtil.formatEmpty(data.get("iscompulsoryinsurance")))){
            sql.append(" and tio.iscompulsoryinsurance=? ");
            params.add(StringUtil.formatEmpty(data.get("iscompulsoryinsurance")));
        }
        if(data.get("saler")!=null && !"".equals(StringUtil.formatEmpty(data.get("saler")))){
            sql.append(" and tc.saler like ? ");
            params.add("%"+StringUtil.formatEmpty(data.get("saler"))+"%");
        }
        sql.append(" order by tio.create_time desc ");
    }

    //--------------------------------------------OA台账报表结束----------------------------------------------------------
    //--------------------------------------------续保OA台账开始----------------------------------------------------------
    public XSSFWorkbook insuranceOrderOAExpExcel(List<Map<String,Object>> orderList,String newPath,String field) throws Exception {
        //查找文件位置
        FileInputStream fs=new FileInputStream(newPath+ File.separator+field+".xlsx");  //获取d://test.xls
        //获取excel
        XSSFWorkbook wb=new XSSFWorkbook(fs);
        XSSFSheet sheet=wb.getSheetAt(0);  //获取到工作表，因为一个excel可能有多个工作表
        XSSFRow row=sheet.getRow(0);  //获取第一行（excel中的行默认从0开始，所以这就是为什么，一个excel必须有字段列头），即，字段列头，便于赋值
        FileOutputStream out=new FileOutputStream(newPath+ File.separator+field+".xlsx");  //向d://test.xls中写数据
        for (int i = 0; i < orderList.size(); i++) {
            Map<String,Object> map=orderList.get(i);
            row=sheet.createRow((short)(sheet.getLastRowNum()+1)); //在现有行号后追加数据
            row.createCell(0).setCellValue(i+1);
            row.createCell(1).setCellValue("".equals(StringUtil.formatEmpty(map.get("carLabel")))?
                    StringUtil.formatEmpty(map.get("carSeries")):StringUtil.formatEmpty(map.get("carLabel")));
            row.createCell(2).setCellValue(StringUtil.formatEmpty(map.get("plateNum")));
            row.createCell(3).setCellValue(StringUtil.formatEmpty(map.get("insured")));
            String insuranceType=StringUtil.formatEmpty(map.get("insuranceType"));

            if (insuranceType.equals(String.valueOf(Constant.INSURNACE_TYPE.add.ordinal()))) {
                insuranceType="追";
            }else if (insuranceType.equals(String.valueOf(Constant.INSURNACE_TYPE.back.ordinal()))) {
                insuranceType="退"+"_"+StringUtil.formatEmpty(map.get("sourceOutDate"));
            }else if ("true".equals(StringUtil.formatEmpty(map.get("continuous")))) {
                insuranceType="联";
            }else{
                insuranceType="否";
            }
            row.createCell(4).setCellValue(insuranceType);
            if(!String.valueOf(Constant.INSURANCE_PROPERTY.jq.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                row.createCell(5).setCellValue(StringUtil.formatEmpty(map.get("insuranceCompanyName")));
                row.createCell(6).setCellValue(StringUtil.formatEmpty(map.get("outOrderDate")));
                row.createCell(7).setCellValue(StringUtil.formatEmpty(map.get("syStartDate")));
            }
            if(!String.valueOf(Constant.INSURANCE_PROPERTY.sy.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                row.createCell(5).setCellValue(StringUtil.formatEmpty(map.get("insuranceCompanyName")));
                row.createCell(6).setCellValue(StringUtil.formatEmpty(map.get("outOrderDate")));
                row.createCell(7).setCellValue(StringUtil.formatEmpty(map.get("jqStartDate")));
            }

            Float syPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("syPrice")))?"0":StringUtil.formatEmpty(map.get("syPrice")));
            Float jqPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("jqPrice")))?"0":StringUtil.formatEmpty(map.get("jqPrice")));
            Float ccsPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("ccsPrice")))?"0":StringUtil.formatEmpty(map.get("ccsPrice")));

            Float itemPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("itemPrice")))?"0":StringUtil.formatEmpty(map.get("itemPrice")));
            Float syAndJqPrice=syPrice+jqPrice;
            syAndJqPrice=syAndJqPrice-itemPrice;
            row.createCell(8).setCellValue(itemPrice);
            row.createCell(9).setCellValue(syPrice);
            if(!String.valueOf(Constant.INSURANCE_PROPERTY.sy.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                row.createCell(10).setCellValue(Constant.formatFloat(jqPrice-itemPrice).toString());
            }

            row.createCell(11).setCellValue(ccsPrice);//车船税

            row.createCell(12).setCellValue(syAndJqPrice+ccsPrice);//合计
            row.createCell(13).setCellValue("true".equals(StringUtil.formatEmpty(map.get("syIsCommissionTemp")))?
                    StringUtil.formatEmpty(map.get("syCommissionTemp")):StringUtil.formatEmpty(map.get("syCommission"))); // 商业险费率

            row.createCell(14).setCellValue("true".equals(StringUtil.formatEmpty(map.get("jqIsCommissionTemp")))?
                    StringUtil.formatEmpty(map.get("jqCommissionTemp")):StringUtil.formatEmpty(map.get("jqCommission"))); // 交强险费率

            row.createCell(15).setCellValue("true".equals(StringUtil.formatEmpty(map.get("ccsIsCommissionTemp")))?
                    StringUtil.formatEmpty(map.get("ccsCommissionTemp")):StringUtil.formatEmpty(map.get("ccsCommission"))); // 车船税费率

            Float syCommissionPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("syCommissionPrice")))?"0":StringUtil.formatEmpty(map.get("syCommissionPrice")));
            Float jqCommissionPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("jqCommissionPrice")))?"0":StringUtil.formatEmpty(map.get("jqCommissionPrice")));
            Float ccsCommissionPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("ccsCommissionPrice")))?"0":StringUtil.formatEmpty(map.get("ccsCommissionPrice")));
            row.createCell(16).setCellValue(syCommissionPrice); // 商业险代理费
            row.createCell(17).setCellValue(jqCommissionPrice); // 交强险代理费
            row.createCell(18).setCellValue(ccsCommissionPrice); // 车船税代理费

            row.createCell(19).setCellValue(syCommissionPrice+jqCommissionPrice+ccsCommissionPrice);

            row.createCell(20).setCellValue("");//合计
            row.createCell(21).setCellValue("");
            Float backPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("backPrice")))?"0":StringUtil.formatEmpty(map.get("backPrice")));
            row.createCell(21).setCellValue("");
            row.createCell(22).setCellValue("");
            row.createCell(23).setCellValue("");

            // 优惠费率
            row.createCell(25).setCellValue("".equals(StringUtil.formatEmpty(map.get("backCommission")))?
                    "":StringUtil.formatEmpty(map.get("backCommission"))); // 优惠费率

            if(!String.valueOf(Constant.INSURANCE_PROPERTY.jq.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))){
                row.createCell(27).setCellValue(backPrice);//商业险返现
            }else{
                row.createCell(28).setCellValue(backPrice);//交强险返现
            }

            row.createCell(29).setCellValue(backPrice);//合计
            row.createCell(30).setCellValue(StringUtil.formatEmpty(map.get("syPrice")));
            if(!String.valueOf(Constant.INSURANCE_PROPERTY.sy.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                row.createCell(31).setCellValue(Constant.formatFloat(jqPrice-itemPrice).toString());
            }
            row.createCell(32).setCellValue(syAndJqPrice);

            if(!String.valueOf(Constant.INSURANCE_PROPERTY.jq.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                row.createCell(33).setCellValue(Constant.formatFloat(((syCommissionPrice+jqCommissionPrice)-backPrice)).toString());//调整后代理费
            }
            if(String.valueOf(Constant.INSURANCE_PROPERTY.jq.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                row.createCell(34).setCellValue((Constant.formatFloat((syCommissionPrice+jqCommissionPrice)-backPrice).toString()));
            }
            row.createCell(35).setCellValue(Constant.formatFloat((syCommissionPrice+jqCommissionPrice)-backPrice).toString());

            row.createCell(36).setCellValue(Constant.getPayType(StringUtil.formatEmpty(map.get("payType"))));
            row.createCell(37).setCellValue(StringUtil.formatEmpty(map.get("couponItmes")));
            row.createCell(38).setCellValue(StringUtil.formatEmpty(map.get("couponPrice")));
            if(!"".equals(StringUtil.formatEmpty(map.get("syAddCommission")))) {
                //非单交强的情况下
                if(!String.valueOf(Constant.INSURANCE_PROPERTY.jq.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                    Float syAddCommisionPrice=Constant.formatFloat(syPrice*((Float.valueOf(StringUtil.formatEmpty(map.get("syAddCommission"))))/100));
                    row.createCell(39).setCellValue(syAddCommisionPrice.toString());//加投
                }
            }else{
                row.createCell(39).setCellValue("");//加投
            }
            row.createCell(40).setCellValue("");

            row.createCell(41).setCellValue("".equals(StringUtil.formatEmpty(map.get("insuranceCommissionerName")))?
                    StringUtil.formatEmpty(map.get("uName")):StringUtil.formatEmpty(map.get("insuranceCommissionerName")));
        }
        out.flush();
        wb.write(out);
        out.close();
        return wb;
    }
    //--------------------------------------------续保OA台账结束----------------------------------------------------------
    //--------------------------------------------续保台账开始------------------------------------------------------------
    private String[] itemidArr={"3","5","7","10","11","13","15","17","25","27"};
    private String[] masterItemArr={"2","4","6","8","18","12","14","16"};
    private String[] otherItemArr={"0","1","9","19","20","21","22","23","24","26"};
    /**
     * 导出续保OA台账
     * @param uid
     * @param data
     * @param newPath
     * @param fileId
     * @return
     * @throws Exception
     */
    @Override
    public XSSFWorkbook insuranceAccountExpExcel(String uid,Map<String,Object> data,String newPath,String fileId) throws Exception {
        //获取表头查询sql
        StringBuffer sql=getHeadToSql();
        List<String> params=new ArrayList<>();
        StringBuffer paramsSql=new StringBuffer();
        //根据条件查询
        expInsuranceOrderParamsToSql(paramsSql,data,params,uid,true);
        //正常、追加保单查询
        List<Map<String,Object>>  orderLists=this.orderDao.list(sql.append(paramsSql).toString(),params.toArray());
        paramsSql=new StringBuffer();
        sql=new StringBuffer();
        //获取表头查询sql
        sql=getHeadToSql();
        params.clear();
        //退单保单 查询
        expInsuranceOrderParamsToSql(paramsSql,data,params,uid,false);
        //合并代码处理
        orderLists.addAll(this.orderDao.list(sql.append(paramsSql).toString(),params.toArray()));
        //查找文件位置
        FileInputStream fs=new FileInputStream(newPath+ File.separator+fileId+".xlsx");  //获取d://test.xls
        //获取excel
        XSSFWorkbook wb=new XSSFWorkbook(fs);
        XSSFSheet sheet=wb.getSheetAt(0);  //获取到工作表，因为一个excel可能有多个工作表
        XSSFRow row=sheet.getRow(0);  //获取第一行（excel中的行默认从0开始，所以这就是为什么，一个excel必须有字段列头），即，字段列头，便于赋值
        FileOutputStream out=new FileOutputStream(newPath+ File.separator+fileId+".xlsx");  //向d://test.xls中写数据
        for(int i=0;i<orderLists.size();i++){
            Map map=orderLists.get(i);
            String insuranceType=StringUtil.formatEmpty(map.get("insuranceType"));
            row=sheet.createRow((short)(sheet.getLastRowNum()+1)); //在现有行号后追加数据
            row.createCell(0).setCellValue(i+1);
            row.createCell(1).setCellValue("".equals(StringUtil.formatEmpty(map.get("insuranceCommissionerName")))?
                    StringUtil.formatEmpty(map.get("uName")):StringUtil.formatEmpty(map.get("insuranceCommissionerName")));
            row.createCell(2).setCellValue(StringUtil.formatEmpty(map.get("outOrderDate")));
            row.createCell(3).setCellValue(StringUtil.formatEmpty(map.get("buyDate")));
            row.createCell(4).setCellValue(StringUtil.formatEmpty(map.get("vin")));
            row.createCell(5).setCellValue(StringUtil.formatEmpty(map.get("contactPhone")));
            row.createCell(6).setCellValue(StringUtil.formatEmpty(map.get("")));
            row.createCell(7).setCellValue("".equals(StringUtil.formatEmpty(map.get("carLabel")))?
                    StringUtil.formatEmpty(map.get("carSeries")):StringUtil.formatEmpty(map.get("carLabel")));
            row.createCell(8).setCellValue(StringUtil.formatEmpty(map.get("plateNum")));
            if(!String.valueOf(Constant.INSURANCE_PROPERTY.jq.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                row.createCell(9).setCellValue(StringUtil.formatEmpty(map.get("insuranceCompanyName")));
                row.createCell(10).setCellValue(StringUtil.formatEmpty(map.get("syContractNo")));
                row.createCell(11).setCellValue(StringUtil.formatEmpty(map.get("outOrderDate")));
                row.createCell(12).setCellValue(StringUtil.formatEmpty(map.get("insured")));
                row.createCell(13).setCellValue(StringUtil.formatEmpty(map.get("syStartDate")));
            }
            String itemIds=StringUtil.formatEmpty(map.get("itemIds"));
            if(itemIds.indexOf("2")!=-1){
                row.createCell(14).setCellValue("✔");
            }
            if(itemIds.indexOf("4")!=-1){
                row.createCell(15).setCellValue("✔");
            }
            if(itemIds.indexOf("6")!=-1){
                row.createCell(16).setCellValue("✔");
            }
            if(itemIds.indexOf("8")!=-1 || itemIds.indexOf("9")!=-1){
                row.createCell(17).setCellValue("✔");
            }
            if(itemIds.indexOf("18")!=-1){
                row.createCell(18).setCellValue("✔");
            }
            if(itemIds.indexOf("12")!=-1){
                row.createCell(19).setCellValue("✔");
            }
            row.createCell(20).setCellValue("");
            if(itemIds.indexOf("16")!=-1){
                row.createCell(21).setCellValue("✔");
            }
            if(itemIds.indexOf("14")!=-1){
                row.createCell(22).setCellValue("✔");
            }
            for(String id:itemidArr){
                if(itemIds.indexOf(id)!=-1){
                    row.createCell(23).setCellValue("✔");
                    break;
                }
            }

            for(String id:otherItemArr){
                if(itemIds.indexOf(id)!=-1){
                    row.createCell(24).setCellValue("✔");
                    break;
                }
            }

            row.createCell(25).setCellValue("");
            if(!String.valueOf(Constant.INSURANCE_PROPERTY.sy.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                row.createCell(26).setCellValue(StringUtil.formatEmpty(map.get("insuranceCompanyName")));
                row.createCell(27).setCellValue(StringUtil.formatEmpty(map.get("jqContractNo")));
                row.createCell(28).setCellValue(StringUtil.formatEmpty(map.get("outOrderDate")));
                row.createCell(29).setCellValue(StringUtil.formatEmpty(map.get("jqStartDate")));
            }
            Float syPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("syPrice")))?"0":StringUtil.formatEmpty(map.get("syPrice")));
            Float jqPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("jqPrice")))?"0":StringUtil.formatEmpty(map.get("jqPrice")));
            Float syAndJqPrice=syPrice+jqPrice;
            row.createCell(30).setCellValue(syPrice);
            Float vvulpt=0f;
            if(!"".equals(StringUtil.formatEmpty(map.get("vvulpt")))){
                vvulpt=Float.valueOf(StringUtil.formatEmpty(map.get("vvulpt")));
            }

            if(String.valueOf(Constant.INSURNACE_TYPE.back.ordinal()).equals(insuranceType)){
                syAndJqPrice=(syAndJqPrice*-1)-vvulpt;
                row.createCell(31).setCellValue(((jqPrice*-1)-vvulpt)*-1);
                row.createCell(33).setCellValue(syAndJqPrice*-1);
            }else{
                row.createCell(31).setCellValue(jqPrice-vvulpt);
                row.createCell(33).setCellValue(syAndJqPrice);
            }
            row.createCell(32).setCellValue(StringUtil.formatEmpty(map.get("vvulpt")));
            row.createCell(33).setCellValue(Constant.formatFloat(syAndJqPrice).toString());

            row.createCell(34).setCellValue("true".equals(StringUtil.formatEmpty(map.get("syIsCommissionTemp")))?
                    StringUtil.formatEmpty(map.get("syCommissionTemp")):StringUtil.formatEmpty(map.get("syCommission")));
            row.createCell(35).setCellValue("true".equals(StringUtil.formatEmpty(map.get("jqIsCommissionTemp")))?
                    StringUtil.formatEmpty(map.get("jqCommissionTemp")):StringUtil.formatEmpty(map.get("jqCommission")));
            Float backPrice=Float.valueOf("".equals(StringUtil.formatEmpty(map.get("backPrice")))?"0":StringUtil.formatEmpty(map.get("backPrice")));
            if(!String.valueOf(Constant.INSURANCE_PROPERTY.jq.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))){
                row.createCell(36).setCellValue(backPrice);//商业险返现
            }else{
                row.createCell(37).setCellValue(backPrice);//交强险返现
            }
            row.createCell(38).setCellValue(StringUtil.formatEmpty(map.get("backPrice")));//合计
            row.createCell(39).setCellValue(Constant.formatFloat(syAndJqPrice).toString());
            row.createCell(40).setCellValue(Constant.getPayType(StringUtil.formatEmpty(map.get("payType"))));
            row.createCell(41).setCellValue("");
            row.createCell(42).setCellValue(StringUtil.formatEmpty(map.get("couponItmes")));
            row.createCell(43).setCellValue(StringUtil.formatEmpty(map.get("couponPrice")));
            if(!"".equals(StringUtil.formatEmpty(map.get("syAddCommission")))) {
                //非单交强的情况下
                if(!String.valueOf(Constant.INSURANCE_PROPERTY.jq.ordinal()).equals(StringUtil.formatEmpty(map.get("iscompulsoryinsurance")))) {
                    Float syAddCommisionPrice=Constant.formatFloat(syPrice*((Float.valueOf(StringUtil.formatEmpty(map.get("syAddCommission"))))/100));
                    row.createCell(44).setCellValue(syAddCommisionPrice.toString());//加投
                }
            }else{
                row.createCell(44).setCellValue("");//加投
            }
            row.createCell(45).setCellValue(StringUtil.formatEmpty(map.get("label")));

        }
        out.flush();
        wb.write(out);
        out.close();
        return wb;
    }


    private StringBuffer getHeadToSql(){
        //查询sql
        StringBuffer sql=new StringBuffer();
        sql.append("SELECT tio.*, items.item_ids,items.vvulpt,ins.label" +
                " FROM" +
                " t_insurance_order_statistics tio" +
                " LEFT JOIN (" +
                " SELECT" +
                " GROUP_CONCAT(item_id) item_ids,company_id,quotation_id,ROUND(SUM(IF (item_id ='0', item_price,'0')),2)  vvulpt" +
                " FROM" +
                " t_insurance_quotation_item" +
                " group by quotation_id,company_id" +
                " ) items ON tio.quotation_id = items.quotation_id AND tio.company_id = items.company_id " +
                " left join (select * from t_bc_newdict  where p_code='insuranceType') ins ON tio.base_type=ins.id "
        );
        return sql;
    }
    //--------------------------------------------续保台账结束------------------------------------------------------------

    //--------------------------------------------保险返利台账报表开始----------------------------------------------------

    /**
     * 保险返利台账报表
     * @param uid
     * @param companyId
     * @param date
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String,Object>> statsticsMonthRebate(String uid,String companyId,String date) throws Exception {
        if("".equals(date)){
            date=DateUtils.getTimeStr();
        }
        if("".equals(companyId)){
            companyId=TokenUtil.getCompanyId(uid);
        }
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        sql.append("select temp.*," +
                " ROUND(temp.sbCommissionPrice-temp.sbBackPrice-temp.sbCouponPrice,2) sbNteProft," +
                " ROUND(temp.xbCommissionPrice-temp.xbBackPrice-temp.xbCouponPrice,2) xbNteProft," +
                " ROUND(temp.sbAllCommissionPrice-temp.sbAllBackPrice-temp.sbAllCouponPrice,2) sbAllNteProft," +
                " ROUND(temp.xbAllCommissionPrice-temp.xbAllBackPrice-temp.xbAllCouponPrice,2) xbAllNteProft" +
                " from (" +
                " select o_name,company_id," +
                " ROUND(SUM(if(DATE_FORMAT(out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m') and insure_type='sb',back_Price,0)),2) sbBackPrice," +
                " ROUND(SUM(if(DATE_FORMAT(out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m') and insure_type='xb',back_Price,0)),2) xbBackPrice," +
                " ROUND(SUM(if(DATE_FORMAT(out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m') and insure_type='sb',coupon_price,0)),2) sbCouponPrice," +
                " ROUND(SUM(if(DATE_FORMAT(out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m') and insure_type='xb',coupon_price,0)),2) xbCouponPrice," +
                " ROUND(SUM(if(DATE_FORMAT(out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m') and insure_type='sb',sy_commission_price+jq_commission_price,0)),2) sbCommissionPrice," +
                " ROUND(SUM(if(DATE_FORMAT(out_order_date,'%Y%m')=DATE_FORMAT('"+date+"','%Y%m') and insure_type='xb',sy_commission_price+jq_commission_price,0)),2) xbCommissionPrice," +
                " ROUND(SUM(if(insure_type='sb',back_Price,0)),2) sbAllBackPrice," +
                " ROUND(SUM(if(insure_type='xb',back_Price,0)),2) xbAllBackPrice," +
                " ROUND(SUM(if(insure_type='sb',coupon_price,0)),2) sbAllCouponPrice," +
                " ROUND(SUM(if(insure_type='xb',coupon_price,0)),2) xbAllCouponPrice," +
                " ROUND(SUM(if(insure_type='sb',sy_commission_price+jq_commission_price,0)),2) sbAllCommissionPrice," +
                " ROUND(SUM(if(insure_type='xb',sy_commission_price+jq_commission_price,0)),2) xbAllCommissionPrice" +
                " from t_insurance_order_statistics where company_id=?" +
                " group by company_id" +
                " )temp");
        return this.orderDao.list(sql.toString(),companyId);
    }
    //--------------------------------------------保险返利台账报表结束----------------------------------------------------


    public static void main(String[] args) throws Exception {
        FileInputStream fs=new FileInputStream("d://xubaotaizhang.xlsx");  //获取d://test.xls
//        POIFSFileSystem ps=new POIFSFileSystem(fs);  //使用POI提供的方法得到excel的信息
        XSSFWorkbook wb=new XSSFWorkbook(fs);
        XSSFSheet sheet=wb.getSheetAt(0);  //获取到工作表，因为一个excel可能有多个工作表
        XSSFRow row=sheet.getRow(0);  //获取第一行（excel中的行默认从0开始，所以这就是为什么，一个excel必须有字段列头），即，字段列头，便于赋值
        System.out.println(sheet.getLastRowNum()+" "+row.getLastCellNum());  //分别得到最后一行的行号，和一条记录的最后一个单元格

        FileOutputStream out=new FileOutputStream("d://xubaotaizhang.xlsx");  //向d://test.xls中写数据
        row=sheet.createRow((short)(sheet.getLastRowNum()+1)); //在现有行号后追加数据
        row.createCell(0).setCellValue("leilei"); //设置第一个（从0开始）单元格的数据
        row.createCell(1).setCellValue(24); //设置第二个（从0开始）单元格的数据


        out.flush();
        wb.write(out);
        out.close();
        System.out.println(row.getPhysicalNumberOfCells()+" "+row.getLastCellNum());
    }
}

